
import numpy as np
import pandas as pd
data = pd.read_csv('../data/stock_data.csv')
data.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 340 entries, 0 to 339
Data columns (total 15 columns):
 #   Column                        Non-Null Count  Dtype  
---  ------                        --------------  -----  
 0   Ticker Symbol                 340 non-null    object 
 1   Security                      340 non-null    object 
 2   GICS Sector                   340 non-null    object 
 3   GICS Sub Industry             340 non-null    object 
 4   Current Price                 340 non-null    float64
 5   Price Change                  340 non-null    float64
 6   Volatility                    340 non-null    float64
 7   ROE                           340 non-null    int64  
 8   Cash Ratio                    340 non-null    int64  
 9   Net Cash Flow                 340 non-null    int64  
 10  Net Income                    340 non-null    int64  
 11  Earnings Per Share            340 non-null    float64
 12  Estimated Shares Outstanding  340 non-null    float64
 13  P/E Ratio                     340 non-null    float64
 14  P/B Ratio                     340 non-null    float64
dtypes: float64(7), int64(4), object(4)
memory usage: 40.0+ KB


object_list=[]
for col in data.columns:
    if data[col].dtype == 'object':
        object_list.append(col)
        # data = data.fillna(data.mode().iloc[0])


object_list

['Ticker Symbol', 'Security', 'GICS Sector', 'GICS Sub Industry']


# Libraries to help with reading and manipulating data
import numpy as np
import pandas as pd

# Libraries to help with data visualization
import matplotlib.pyplot as plt
import seaborn as sns

sns.set_theme(style='darkgrid')

# Removes the limit for the number of displayed columns
pd.set_option("display.max_columns", None)
# Sets the limit for the number of displayed rows
pd.set_option("display.max_rows", 200)

# to scale the data using z-score
from sklearn.preprocessing import StandardScaler

# to compute distances
from scipy.spatial.distance import cdist, pdist

# to perform k-means clustering and compute silhouette scores
from sklearn.cluster import KMeans
from sklearn.metrics import silhouette_score

# to visualize the elbow curve and silhouette scores
from yellowbrick.cluster import KElbowVisualizer, SilhouetteVisualizer

# to perform hierarchical clustering, compute cophenetic correlation, and create dendrograms
from sklearn.cluster import AgglomerativeClustering
from scipy.cluster.hierarchy import dendrogram, linkage, cophenet

# to suppress warnings
import warnings
warnings.filterwarnings("ignore")


# ChatGPT3 recommended libraries:
from scipy.spatial.distance import pdist, squareform

# https://docs.xlwings.org/en/latest/jupyternotebooks.html
from xlwings import view
from xlwings import load


data = pd.read_csv('../data/stock_data.csv')


data.head(2)


# checking shape of the data
print(f"There are {data.shape[0]} rows and {data.shape[1]} columns.") ## Complete the code to get the shape of data

There are 340 rows and 15 columns.


# let's view a sample of the data
data.sample(n=4, random_state=1)


# checking the column names and datatypes
data.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 340 entries, 0 to 339
Data columns (total 15 columns):
 #   Column                        Non-Null Count  Dtype  
---  ------                        --------------  -----  
 0   Ticker Symbol                 340 non-null    object 
 1   Security                      340 non-null    object 
 2   GICS Sector                   340 non-null    object 
 3   GICS Sub Industry             340 non-null    object 
 4   Current Price                 340 non-null    float64
 5   Price Change                  340 non-null    float64
 6   Volatility                    340 non-null    float64
 7   ROE                           340 non-null    int64  
 8   Cash Ratio                    340 non-null    int64  
 9   Net Cash Flow                 340 non-null    int64  
 10  Net Income                    340 non-null    int64  
 11  Earnings Per Share            340 non-null    float64
 12  Estimated Shares Outstanding  340 non-null    float64
 13  P/E Ratio                     340 non-null    float64
 14  P/B Ratio                     340 non-null    float64
dtypes: float64(7), int64(4), object(4)
memory usage: 40.0+ KB


# copying the data to another variable to avoid any changes to original data
df = data.copy()


# checking for duplicate values
df.duplicated().sum() ## Complete the code to get total number of duplicate values

0


def isna_checker():
    """
    function to check if the count in each col of data is 0. 
    """
    df_isna=df.isna().sum() # full dataframe
    found_na=False
    for i in df_isna.keys(): # take 1 key at a time out of just the keys
        if df_isna==0:
            pass
    # inform user
    if found_na==False:
        print('no na values in data')
    else:
        print('the following columns have na values:')


object_na_counts={}
df_isna=df.isna().sum()
dnaky=df_isna.keys()
dnavl=df_isna.array
for index, col_na_count in enumerate(df_isna):
    # print(index)
    object_na_counts.update({dnaky[index]:dnavl[index]})
object_na_counts
# reference: [pandas.DataFrame.values](https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.values.html)
# reference: [pandas.Series.array](https://pandas.pydata.org/docs/reference/api/pandas.Series.array.html#pandas.Series.array)
# dnavl[0]

{'Ticker Symbol': 0,
 'Security': 0,
 'GICS Sector': 0,
 'GICS Sub Industry': 0,
 'Current Price': 0,
 'Price Change': 0,
 'Volatility': 0,
 'ROE': 0,
 'Cash Ratio': 0,
 'Net Cash Flow': 0,
 'Net Income': 0,
 'Earnings Per Share': 0,
 'Estimated Shares Outstanding': 0,
 'P/E Ratio': 0,
 'P/B Ratio': 0}


object_na_counts.keys()

dict_keys(['Ticker Symbol', 'Security', 'GICS Sector', 'GICS Sub Industry', 'Current Price', 'Price Change', 'Volatility', 'ROE', 'Cash Ratio', 'Net Cash Flow', 'Net Income', 'Earnings Per Share', 'Estimated Shares Outstanding', 'P/E Ratio', 'P/B Ratio'])


object_na_counts.values()

dict_values([0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0])


sum_object_na_counts=0
for index, value in enumerate(object_na_counts.values()):
    sum_object_na_counts+=list(object_na_counts.values())[index]
if (sum_object_na_counts==0):
    print("no na values in dataframe")

no na values in dataframe


df.describe(include='all').T


object_list

['Ticker Symbol', 'Security', 'GICS Sector', 'GICS Sub Industry']


# function to plot a boxplot and a histogram along the same scale.


def histogram_boxplot(df, feature, figsize=(8, 5), kde=False, bins=None):
    """
    Boxplot and histogram combined

    data: dataframe
    feature: dataframe column
    figsize: size of figure (default (12,7))
    kde: whether to the show density curve (default False)
    bins: number of bins for histogram (default None)
    """
    f2, (ax_box2, ax_hist2) = plt.subplots(
        nrows=2,  # Number of rows of the subplot grid= 2
        sharex=True,  # x-axis will be shared among all subplots
        gridspec_kw={"height_ratios": (0.25, 0.75)},
        figsize=figsize,
    )  # creating the 2 subplots
    sns.boxplot(
        data=df, x=feature, ax=ax_box2, showmeans=True, color="violet"
    )  # boxplot will be created and a star will indicate the mean value of the column
    sns.histplot(
        data=df, x=feature, kde=kde, ax=ax_hist2, bins=bins, palette="winter"
    ) if bins else sns.histplot(
        data=df, x=feature, kde=kde, ax=ax_hist2
    )  # For histogram
    ax_hist2.axvline(
        df[feature].mean(), color="green", linestyle="--"
    )  # Add mean to the histogram
    ax_hist2.axvline(
        df[feature].median(), color="black", linestyle="-"
    )  # Add median to the histogram


histogram_boxplot(df, 'Current Price')


df['Current Price'].describe() # GitHub Copilot assisted line

count     340.000000
mean       80.862345
std        98.055086
min         4.500000
25%        38.555000
50%        59.705000
75%        92.880001
max      1274.949951
Name: Current Price, dtype: float64


df['Current Price'].describe()

count     340.000000
mean       80.862345
std        98.055086
min         4.500000
25%        38.555000
50%        59.705000
75%        92.880001
max      1274.949951
Name: Current Price, dtype: float64


df['Current Price'].describe().keys()

Index(['count', 'mean', 'std', 'min', '25%', '50%', '75%', 'max'], dtype='object')


df['Current Price'].describe().keys()[0]

'count'


IQR=[]
Q1=0
Q3=0
for x in df['Current Price'].describe().keys():
    if x== '25%':
        Q1=x
    if x == '75%':
        Q3=x
    IQR=[Q1,Q3]
    
# df['Current Price'].describe().keys()[i]


Q1=IQR[0]
Q3=IQR[1]
# IQR_no=Q1-Q3

Q1

'25%'


for a in df['Current Price'].describe().keys():
    if a=='25%':
        Q1_val=(df['Current Price'].describe()[a])


Q1_val

38.55499975


for a in df['Current Price'].describe().keys():
    if a=='75%':
        Q3_val=(df['Current Price'].describe()[a])


Q3_val

92.88000124999999


IQR_val = Q3_val - Q1_val


IQR_val

54.32500149999999


df['Current Price'].describe()

count     340.000000
mean       80.862345
std        98.055086
min         4.500000
25%        38.555000
50%        59.705000
75%        92.880001
max      1274.949951
Name: Current Price, dtype: float64


df['Current Price'].describe()

count     340.000000
mean       80.862345
std        98.055086
min         4.500000
25%        38.555000
50%        59.705000
75%        92.880001
max      1274.949951
Name: Current Price, dtype: float64


round(Q1_val,3)

38.555


Q1_val_round_3_to_2=round(Q1_val,3)


round(Q1_val_round_3_to_2,2) # rounds to 38.56

38.56


round(Q1_val,2) # rounds to 38.55

38.55


Q3_val_round_3_to_2=round(Q3_val,3)


round(Q3_val_round_3_to_2,2)

92.88


round(round(Q3_val,3),2)

92.88


IQR_val

54.32500149999999


def two_decimal_correct(*args):
    """
    pass *args as list
    takes floats
    returns list_of_outputs rounded to 2 decimal places
    Purpose of function:
    - to avoid error rounding from float to 2 decimal places
    - by first rounding to 3 and then to 2
    - which seems to fix bug of:
        - rounding directly to 2
        - can result in rounding down
        - even when next decimal place is >=5
    """
    list_of_outputs=[]
    for new_input in args:
        new_output=round(round(new_input,3),2)
        list_of_outputs.append(new_output)
    return list_of_outputs


two_decimal_correct(Q3_val, Q1_val, IQR_val)

[92.88, 38.56, 54.32]


type(two_decimal_correct(Q3_val, Q1_val, IQR_val))

list


type(IQR_val)

numpy.float64


[IQR_val]

[54.32500149999999]


two_decimal_correct(IQR_val)

[54.32]


two_decimal_correct(IQR_val)[0]

54.32


df['Current Price'].describe()

count     340.000000
mean       80.862345
std        98.055086
min         4.500000
25%        38.555000
50%        59.705000
75%        92.880001
max      1274.949951
Name: Current Price, dtype: float64


print('The interquartile range (IQR) (Q3 - Q1) ({Q3_val_2:.2f}-{Q1_val_2:.2f}) (92.88 - 38.56) is $'.format(Q3_val_2=two_decimal_correct(Q3_val)[0],Q1_val_2=two_decimal_correct(Q1_val)[0]),'{IQR_val_2:.2f}'.format(IQR_val_2=two_decimal_correct(IQR_val)[0]))

The interquartile range (IQR) (Q3 - Q1) (92.88-38.56) (92.88 - 38.56) is $ 54.32


df['Current Price'].quantile(0.25)

38.55499975


histogram_boxplot(df, 'Price Change')  ## Complete the code to create histogram_boxplot for 'Price Change'


df['Price Change'].describe()

count    340.000000
mean       4.078194
std       12.006338
min      -47.129693
25%       -0.939484
50%        4.819505
75%       10.695493
max       55.051683
Name: Price Change, dtype: float64


float("%.2f" % df['Price Change'].mean()) # resolved: cast string to float

4.08


print("mean of Price Change was $", float("%.2f" % df['Price Change'].mean()), sep='')

mean of Price Change was $4.08


print("median of Price Change was $", float("%.2f" % df['Price Change'].median()), sep='')

median of Price Change was $4.82


print("standard deviation for Price Change was $", float("%.2f" % df['Price Change'].std()), sep='')

standard deviation for Price Change was $12.01


print("minimum Price Change was $", float("%.2f" % df['Price Change'].min()), sep='')

minimum Price Change was $-47.13


print("maximum Price Change was $", float("%.2f" % df['Price Change'].max()), sep='')

maximum Price Change was $55.05


feature='Volatility'


histogram_boxplot(df=df,feature='Volatility')  ## Complete the code to create histogram_boxplot for 'Volatility'


def function_arb(a,y):
    if y == '.mean()':
        return a.mean()
    elif y == '.median()':
        return a.median()
    elif y == '.std()':
        return a.std()
    elif y == '.min()':
        return a.min()
    elif y == '.max()':
        return a.max()


for x, y in zip(['mean', 'median', 'standard deviation', 'minimum', 'maximum'], ['.mean()', '.median()', '.std()', '.min()', '.max()']):
    a=df[feature]
    returned=function_arb(a,y)
    print(str("{x2} {feature_2} was $".format(x2=x,feature_2=feature)),float("%.2f" % returned))

mean Volatility was $ 1.53
median Volatility was $ 1.39
standard deviation Volatility was $ 0.59
minimum Volatility was $ 0.73
maximum Volatility was $ 4.58


feature='ROE'


histogram_boxplot(df=df,feature=feature)  ## Complete the code to create histogram_boxplot for 'ROE'


def function_arb(a,y):
    if y == '.mean()':
        return a.mean()
    elif y == '.median()':
        return a.median()
    elif y == '.std()':
        return a.std()
    elif y == '.min()':
        return a.min()
    elif y == '.max()':
        return a.max()
    
for x, y in zip(['mean', 'median', 'standard deviation', 'minimum', 'maximum'], ['.mean()', '.median()', '.std()', '.min()', '.max()']):
    a=df[feature]
    returned=function_arb(a,y)
    print(str("{x2} {feature_2} was $".format(x2=x,feature_2=feature)),float("%.2f" % returned))

mean ROE was $ 39.6
median ROE was $ 15.0
standard deviation ROE was $ 96.55
minimum ROE was $ 1.0
maximum ROE was $ 917.0


feature='Cash Ratio'


histogram_boxplot(df=df,feature=feature)  ## Complete the code to create histogram_boxplot for 'Cash Ratio'


def function_arb(a,y):
    if y == '.mean()':
        return a.mean()
    elif y == '.median()':
        return a.median()
    elif y == '.std()':
        return a.std()
    elif y == '.min()':
        return a.min()
    elif y == '.max()':
        return a.max()
    
for x, y in zip(['mean', 'median', 'standard deviation', 'minimum', 'maximum'], ['.mean()', '.median()', '.std()', '.min()', '.max()']):
    a=df[feature]
    returned=function_arb(a,y)
    print(str("{x2} {feature_2} was $".format(x2=x,feature_2=feature)),float("%.2f" % returned))

mean Cash Ratio was $ 70.02
median Cash Ratio was $ 47.0
standard deviation Cash Ratio was $ 90.42
minimum Cash Ratio was $ 0.0
maximum Cash Ratio was $ 958.0


feature='Net Cash Flow'


histogram_boxplot(df=df,feature=feature)  ## Complete the code to create histogram_boxplot for 'Net Cash Flow'


def function_arb(a,y):
    if y == '.mean()':
        return a.mean()
    elif y == '.median()':
        return a.median()
    elif y == '.std()':
        return a.std()
    elif y == '.min()':
        return a.min()
    elif y == '.max()':
        return a.max()
    
for x, y in zip(['mean', 'median', 'standard deviation', 'minimum', 'maximum'], ['.mean()', '.median()', '.std()', '.min()', '.max()']):
    a=df[feature]
    returned=function_arb(a,y)
    print(str("{x2} {feature_2} was $".format(x2=x,feature_2=feature)),float("%.2f" % returned))

mean Net Cash Flow was $ 55537620.59
median Net Cash Flow was $ 2098000.0
standard deviation Net Cash Flow was $ 1946365312.18
minimum Net Cash Flow was $ -11208000000.0
maximum Net Cash Flow was $ 20764000000.0


feature='Net Income'


histogram_boxplot(df=df,feature=feature)  ## Complete the code to create histogram_boxplot for 'Net Income'


def function_arb(a,y):
    if y == '.mean()':
        return a.mean()
    elif y == '.median()':
        return a.median()
    elif y == '.std()':
        return a.std()
    elif y == '.min()':
        return a.min()
    elif y == '.max()':
        return a.max()
    
for x, y in zip(['mean', 'median', 'standard deviation', 'minimum', 'maximum'], ['.mean()', '.median()', '.std()', '.min()', '.max()']):
    a=df[feature]
    returned=function_arb(a,y)
    print(str("{x2} {feature_2} was $".format(x2=x,feature_2=feature)),float("%.2f" % returned))

mean Net Income was $ 1494384602.94
median Net Income was $ 707336000.0
standard deviation Net Income was $ 3940150279.33
minimum Net Income was $ -23528000000.0
maximum Net Income was $ 24442000000.0


feature='Earnings Per Share'


histogram_boxplot(df=df,feature=feature)  ## Complete the code to create histogram_boxplot for 'Earnings Per Share'


def function_arb(a,y):
    if y == '.mean()':
        return a.mean()
    elif y == '.median()':
        return a.median()
    elif y == '.std()':
        return a.std()
    elif y == '.min()':
        return a.min()
    elif y == '.max()':
        return a.max()
    
for x, y in zip(['mean', 'median', 'standard deviation', 'minimum', 'maximum'], ['.mean()', '.median()', '.std()', '.min()', '.max()']):
    a=df[feature]
    returned=function_arb(a,y)
    print(str("{x2} {feature_2} was $".format(x2=x,feature_2=feature)),float("%.2f" % returned))

mean Earnings Per Share was $ 2.78
median Earnings Per Share was $ 2.9
standard deviation Earnings Per Share was $ 6.59
minimum Earnings Per Share was $ -61.2
maximum Earnings Per Share was $ 50.09


feature='Estimated Shares Outstanding'


histogram_boxplot(df=df,feature=feature)  ## Complete the code to create histogram_boxplot for 'Estimated Shares Outstanding'


def function_arb(a,y):
    if y == '.mean()':
        return a.mean()
    elif y == '.median()':
        return a.median()
    elif y == '.std()':
        return a.std()
    elif y == '.min()':
        return a.min()
    elif y == '.max()':
        return a.max()
    
for x, y in zip(['mean', 'median', 'standard deviation', 'minimum', 'maximum'], ['.mean()', '.median()', '.std()', '.min()', '.max()']):
    a=df[feature]
    returned=function_arb(a,y)/10e6
    print(str("{x2} {feature_2} was $".format(x2=x,feature_2=feature)),float("%.2f" % returned), ' million', sep='')

mean Estimated Shares Outstanding was $57.7 million
median Estimated Shares Outstanding was $30.97 million
standard deviation Estimated Shares Outstanding was $84.58 million
minimum Estimated Shares Outstanding was $2.77 million
maximum Estimated Shares Outstanding was $615.93 million


feature='P/E Ratio'


histogram_boxplot(df=df,feature=feature)  ## Complete the code to create histogram_boxplot for 'P/E Ratio'


def function_arb(a,y):
    if y == '.mean()':
        return a.mean()
    elif y == '.median()':
        return a.median()
    elif y == '.std()':
        return a.std()
    elif y == '.min()':
        return a.min()
    elif y == '.max()':
        return a.max()
    
for x, y in zip(['mean', 'median', 'standard deviation', 'minimum', 'maximum'], ['.mean()', '.median()', '.std()', '.min()', '.max()']):
    a=df[feature]
    returned=function_arb(a,y)
    print(str("{x2} {feature_2} was $".format(x2=x,feature_2=feature)),float("%.2f" % returned), sep='')

mean P/E Ratio was $32.61
median P/E Ratio was $20.82
standard deviation P/E Ratio was $44.35
minimum P/E Ratio was $2.94
maximum P/E Ratio was $528.04


feature='P/B Ratio'


histogram_boxplot(df=df,feature=feature)  ## Complete the code to create histogram_boxplot for 'P/B Ratio'


def function_arb(a,y):
    if y == '.mean()':
        return a.mean()
    elif y == '.median()':
        return a.median()
    elif y == '.std()':
        return a.std()
    elif y == '.min()':
        return a.min()
    elif y == '.max()':
        return a.max()
    
for x, y in zip(['mean', 'median', 'standard deviation', 'minimum', 'maximum'], ['.mean()', '.median()', '.std()', '.min()', '.max()']):
    a=df[feature]
    returned=function_arb(a,y)
    print(str("{x2} {feature_2} was $".format(x2=x,feature_2=feature)),float("%.2f" % returned), sep='')

mean P/B Ratio was $-1.72
median P/B Ratio was $-1.07
standard deviation P/B Ratio was $13.97
minimum P/B Ratio was $-76.12
maximum P/B Ratio was $129.06


# function to create labeled barplots


def labeled_barplot(df, feature, perc=False, n=None, figsize=None):
    """
    Barplot with percentage at the top

    data: dataframe
    feature: dataframe column
    perc: whether to display percentages instead of count (default is False)
    n: displays the top n category levels (default is None, i.e., display all levels)
    """

    total = len(df[feature])  # length of the column
    count = df[feature].nunique()
    if figsize==None:
        if n is None:
            plt.figure(figsize=(count + 1, 5))
        else:
            plt.figure(figsize=(n + 1, 5))
    else:
        plt.figure(figsize=figsize)
    plt.xticks(rotation=90, fontsize=14)
    ax = sns.countplot(
        data=df,
        x=feature,
        palette="Paired",
        order=df[feature].value_counts().index[:n].sort_values(),
    )

    for p in ax.patches:
        if perc == True:
            label = "{:.1f}%".format(
                100 * p.get_height() / total
            )  # percentage of each class of the category
        else:
            label = p.get_height()  # count of each level of the category

        x = p.get_x() + p.get_width() / 2  # width of the plot
        y = p.get_height()  # height of the plot

        ax.annotate(
            label,
            (x, y),
            ha="center",
            va="center",
            size=12,
            xytext=(0, 5),
            textcoords="offset points",
        )  # annotate the percentage

    plt.show()  # show the plot


labeled_barplot(df, 'GICS Sector', perc=True, figsize=(7,1))


type(df['GICS Sector'].value_counts().index)
# how to unpack pandas index

pandas.core.indexes.base.Index


df['GICS Sector'].apply(pd.Series).head(3)


GICS_Sector_Series_as_df=df['GICS Sector'].apply(pd.Series)


GICS_Sector_Series_as_df.keys()

RangeIndex(start=0, stop=1, step=1)


type(GICS_Sector_Series_as_df)

pandas.core.frame.DataFrame


GICS_Sector_Series_as_df.keys() # RangeIndex(start=0, stop=1, step=1)
type(GICS_Sector_Series_as_df.keys())
GICS_Sector_Series_as_df.keys()

RangeIndex(start=0, stop=1, step=1)


print(GICS_Sector_Series_as_df.keys())

RangeIndex(start=0, stop=1, step=1)


print(GICS_Sector_Series_as_df.columns)

RangeIndex(start=0, stop=1, step=1)


print(GICS_Sector_Series_as_df.columns)

RangeIndex(start=0, stop=1, step=1)


print(GICS_Sector_Series_as_df.keys().tolist())

[0]


GICS_Sector_Series_as_df.head(3)


type(GICS_Sector_Series_as_df[0])

pandas.core.series.Series


GICS_Sector_Series_as_df.index

RangeIndex(start=0, stop=340, step=1)


GICS_Sector_Series_as_df.index.tolist()[:8] # 0 to 7 of all 340 index values

[0, 1, 2, 3, 4, 5, 6, 7]


len(GICS_Sector_Series_as_df.index.tolist()) # length or number of values in this dataframe from Series index is list of 340 values

340


sub_ind_order_counts=df['GICS Sub Industry'].sort_values(inplace=False).value_counts()


sub_ind_order_counts.head(4)

GICS Sub Industry
Oil & Gas Exploration & Production    16
REITs                                 14
Industrial Conglomerates              14
Internet Software & Services          12
Name: count, dtype: int64


df_GICS=df.copy()


# sub_ind_order_counts=df['GICS Sub Industry'].sort_values(inplace=False).value_counts()

df_GICS_sorted=df_GICS['GICS Sub Industry'].sort_values().value_counts()


df_GICS.sort_values(by=['GICS Sub Industry'],inplace=True)


df_GICS_sorted.head(3)

GICS Sub Industry
Oil & Gas Exploration & Production    16
REITs                                 14
Industrial Conglomerates              14
Name: count, dtype: int64


type(df_GICS_sorted)

pandas.core.series.Series


# Note1: convert pandas Series to DataFrame using above reference 2.

# operates on df_GICS_sorted Series

# which is a sorted copy of df
    # sorted for the purpose of getting a sorted plot when fed into 
        # labeled_barplot plotting function

df_GICS_sorted.to_frame().head(4)


df_GICS_sorted.head(3)

GICS Sub Industry
Oil & Gas Exploration & Production    16
REITs                                 14
Industrial Conglomerates              14
Name: count, dtype: int64


df_GICS_sorted_to_df=df_GICS_sorted.to_frame()


type(df_GICS_sorted)

pandas.core.series.Series


type(df_GICS_sorted_to_df)

pandas.core.frame.DataFrame


labeled_barplot(df_GICS_sorted_to_df.sort_values(by=['count']), feature='count', perc=True, figsize=(9,1))


df.sample(1)


df_3=df.copy()
df_3.head(1) # DataFrame


lookup_table=df_3['GICS Sub Industry'].value_counts()


lookup_table.head(4)

GICS Sub Industry
Oil & Gas Exploration & Production    16
REITs                                 14
Industrial Conglomerates              14
Electric Utilities                    12
Name: count, dtype: int64


# from def labeled_barplot(df, feature, perc=False, n=None)
# use:
# sns.countplot
# plt.show()
def labeled_barplot_2(df, feature, perc=False, n=None):
    
    total = len(df[feature])  # length of the column
    count = df[feature].nunique()
    if n is None:
        plt.figure(figsize=(count + 1, 5))
    else:
        plt.figure(figsize=(n + 1, 5))

    
    sns.countplot(data=df_3, x='GICS Sub Industry', palette="Paired", order=df_3['GICS Sub Industry'].value_counts().index)
    plt.xticks(rotation=90, fontsize=10)
    ax = sns.countplot(
        data=df,
        x=feature,
        palette="Paired",
        order=df[feature].value_counts().index[:n].sort_values(),
    )
    for p in ax.patches:
        if perc == True:
            label = "{:.1f}%".format(
                100 * p.get_height() / total
            )  # percentage of each class of the category
        else:
            label = p.get_height()  # count of each level of the category

        x = p.get_x() + p.get_width() / 2  # width of the plot
        y = p.get_height()  # height of the plot

    ax.annotate(
        label,
        (x, y),
        ha="center",
        va="center",
        size=12,
        xytext=(0, 5),
        textcoords="offset points",
    )  # annotate the percentage


    plt.show()


df_3['GICS Sub Industry'].value_counts().index[:10].sort_values()

Index(['Banks', 'Diversified Financial Services', 'Electric Utilities',
       'Health Care Equipment', 'Industrial Conglomerates',
       'Internet Software & Services', 'MultiUtilities',
       'Oil & Gas Exploration & Production', 'Property & Casualty Insurance',
       'REITs'],
      dtype='object', name='GICS Sub Industry')


df_3['GICS Sub Industry']

0                          Airlines
1                   Pharmaceuticals
2             Health Care Equipment
3              Application Software
4                    Semiconductors
                   ...             
335    Internet Software & Services
336                     Restaurants
337           Health Care Equipment
338                  Regional Banks
339                 Pharmaceuticals
Name: GICS Sub Industry, Length: 340, dtype: object


labeled_barplot(df, 'GICS Sub Industry', perc=True)  ## Complete the code to create a labelled barplot for 'GICS Sub Industry'


type(sub_ind_order_counts)

pandas.core.series.Series


def labeled_barplot_6(df, feature, perc=False, n=None, sort_by_count=False):
    """
    Barplot with percentage at the top

    data: dataframe
    feature: dataframe column
    perc: whether to display percentages instead of count (default is False)
    n: displays the top n category levels (default is None, i.e., display all levels)
    sort_by_count: whether to sort the categories by count (default is False)
    """

    total = len(df[feature])  # length of the column
    count = df[feature].nunique()
    if n is None:
        plt.figure(figsize=(count + 1, 5))
    else:
        plt.figure(figsize=(n + 1, 5))

    plt.xticks(rotation=90, fontsize=15)
    if sort_by_count:
        ax = sns.countplot(
            data=df,
            x=feature,
            palette="Paired",
            order=df[feature].value_counts().sort_values(ascending=False).index[:n],
        )
    else:
        ax = sns.countplot(
            data=df,
            x=feature,
            palette="Paired",
            order=df[feature].sort_values().unique()[:n],
        )

    for p in ax.patches:
        if perc == True:
            label = "{:.1f}%".format(
                100 * p.get_height() / total
            )  # percentage of each class of the category


labeled_barplot_6(df, 'GICS Sub Industry', perc=True, n=None, sort_by_count=True)
# where n is the number of sub sectors to display on the countplot


# df.info()
df["Security"].head(1)

0    American Airlines Group
Name: Security, dtype: object


df_nameless = df.drop(["Security", "Ticker Symbol"], axis=1)


sns.pairplot(df)

<seaborn.axisgrid.PairGrid at 0x2cda3023070>


df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 340 entries, 0 to 339
Data columns (total 15 columns):
 #   Column                        Non-Null Count  Dtype  
---  ------                        --------------  -----  
 0   Ticker Symbol                 340 non-null    object 
 1   Security                      340 non-null    object 
 2   GICS Sector                   340 non-null    object 
 3   GICS Sub Industry             340 non-null    object 
 4   Current Price                 340 non-null    float64
 5   Price Change                  340 non-null    float64
 6   Volatility                    340 non-null    float64
 7   ROE                           340 non-null    int64  
 8   Cash Ratio                    340 non-null    int64  
 9   Net Cash Flow                 340 non-null    int64  
 10  Net Income                    340 non-null    int64  
 11  Earnings Per Share            340 non-null    float64
 12  Estimated Shares Outstanding  340 non-null    float64
 13  P/E Ratio                     340 non-null    float64
 14  P/B Ratio                     340 non-null    float64
dtypes: float64(7), int64(4), object(4)
memory usage: 40.0+ KB


# list comprehensions per chatgpt3
idx_cols=[c for c in df.columns if df[c].dtype in ['object']] # GitHub Copilot
valu_cols=[c for c in df.columns if df[c].dtype in ['float64', 'int64']] # GitHub Copilot, Tabnine AI


pivot = df.pivot_table(values=valu_cols, index=idx_cols, aggfunc='mean') # GitHub Copilot, Tabnine AI


# NameError: name 'squareform' is not defined # ChatgGPT3
# from scipy.spatial.distance import pdist, squareform
row_dist=pd.DataFrame(squareform(pdist(pivot, metric='euclidean')), columns=pivot.index, index=pivot.index) # GitHub Copilot, Tabnine AI, ChatGPT3


row_clusters = linkage(row_dist, method='complete')


fig = plt.figure(figsize=(8,8))
axd = fig.add_axes([0.09,0.1,0.2,0.6])
row_dendr = dendrogram(row_clusters, orientation='left')


df_rowclust = pivot.iloc[row_dendr['leaves']]


plt.figure(figsize=(9, 5))
sns.heatmap(
    df_rowclust.corr(), annot=True, vmin=-1, vmax=1, fmt=".2f", cmap="Spectral"
)
plt.show()


# loads from excel using xlwings
# load()


pd.read_excel('../data/heatmap_insights_analysis.xlsx')


plt.figure(figsize=(6, 1.5))
sns.barplot(data=df, x='GICS Sector', y='Price Change', ci=False) ## Complete the code to choose the right variables
plt.xticks(rotation=90)
plt.show()


plt.figure(figsize=(5,1.2))
sns.barplot(data=df, x='GICS Sector', y='Cash Ratio', ci=False)  ## Complete the code to choose the right variables
plt.xticks(rotation=90)
plt.show()


plt.figure(figsize=(5,1))
sns.barplot(data=df, x='GICS Sector', y='P/E Ratio', ci=False)  ## Complete the code to choose the right variables
plt.xticks(rotation=90)
plt.show()


# rerun pd.read_excel() and save to variable
Exxon2021=pd.read_excel('../data/Balance-Sheet-Exxon-Mobil-2021.xlsx')


Exxon2021


plt.figure(figsize=(5,1))
sns.barplot(data=df, x='GICS Sector', y='Volatility', ci=False)  ## Complete the code to choose the right variables
plt.xticks(rotation=90)
plt.show()


plt.figure(figsize=(15, 12))

numeric_columns = df.select_dtypes(include=np.number).columns.tolist()

for i, variable in enumerate(numeric_columns):
    plt.subplot(3, 4, i + 1)
    plt.boxplot(df[variable], whis=1.5)
    plt.tight_layout()
    plt.title(variable)

plt.show()


# scaling the data before clustering
scaler = StandardScaler()
subset = df[numeric_columns].copy()  ## Complete the code to scale the data
subset_scaled = scaler.fit_transform(subset)


# creating a dataframe of the scaled data
subset_scaled_df = pd.DataFrame(subset_scaled, columns=subset.columns)


k_means_df = subset_scaled_df.copy()


clusters = range(1, 15)
meanDistortions = []

for k in clusters:
    model = KMeans(n_clusters=k, random_state=1)
    model.fit(subset_scaled_df)
    prediction = model.predict(k_means_df)
    distortion = (
        sum(np.min(cdist(k_means_df, model.cluster_centers_, "euclidean"), axis=1))
        / k_means_df.shape[0]
    )

    meanDistortions.append(distortion)

    print("Number of Clusters:", k, "\tAverage Distortion:", distortion)

plt.plot(clusters, meanDistortions, "bx-")
plt.xlabel("k")
plt.ylabel("Average Distortion")
plt.title("Selecting k with the Elbow Method", fontsize=20)
plt.show()

Number of Clusters: 1 	Average Distortion: 2.5425069919221697
Number of Clusters: 2 	Average Distortion: 2.382318498894466
Number of Clusters: 3 	Average Distortion: 2.2692367155390745
Number of Clusters: 4 	Average Distortion: 2.1745559827866363
Number of Clusters: 5 	Average Distortion: 2.128799332840716
Number of Clusters: 6 	Average Distortion: 2.080400099226289
Number of Clusters: 7 	Average Distortion: 2.0289794220177395
Number of Clusters: 8 	Average Distortion: 1.964144163389972
Number of Clusters: 9 	Average Distortion: 1.9221492045198068
Number of Clusters: 10 	Average Distortion: 1.8513913649973124
Number of Clusters: 11 	Average Distortion: 1.8024134734578485
Number of Clusters: 12 	Average Distortion: 1.7900931879652673
Number of Clusters: 13 	Average Distortion: 1.7417609203336912
Number of Clusters: 14 	Average Distortion: 1.673559857259703


model = KMeans(random_state=1)
visualizer = KElbowVisualizer(model, k=(1, 15), timings=True)
visualizer.fit(k_means_df)  # fit the data to the visualizer
visualizer.show()  # finalize and render figure

<Axes: title={'center': 'Distortion Score Elbow for KMeans Clustering'}, xlabel='k', ylabel='distortion score'>


sil_score = []
cluster_list = range(2, 15)
for n_clusters in cluster_list:
    clusterer = KMeans(n_clusters=n_clusters, random_state=1)
    preds = clusterer.fit_predict((subset_scaled_df))
    score = silhouette_score(k_means_df, preds)
    sil_score.append(score)
    print("For n_clusters = {}, the silhouette score is {})".format(n_clusters, score))

plt.plot(cluster_list, sil_score)
plt.show()

For n_clusters = 2, the silhouette score is 0.43969639509980457)
For n_clusters = 3, the silhouette score is 0.4644405674779404)
For n_clusters = 4, the silhouette score is 0.4577225970476733)
For n_clusters = 5, the silhouette score is 0.43228336443659804)
For n_clusters = 6, the silhouette score is 0.4005422737213617)
For n_clusters = 7, the silhouette score is 0.3976335364987305)
For n_clusters = 8, the silhouette score is 0.40278401969450467)
For n_clusters = 9, the silhouette score is 0.3778585981433699)
For n_clusters = 10, the silhouette score is 0.13458938329968687)
For n_clusters = 11, the silhouette score is 0.1421832155528444)
For n_clusters = 12, the silhouette score is 0.2044669621527429)
For n_clusters = 13, the silhouette score is 0.23424874810104204)
For n_clusters = 14, the silhouette score is 0.12102526472829901)


model = KMeans(random_state=1)
visualizer = KElbowVisualizer(model, k=(2, 15), metric="silhouette", timings=True)
visualizer.fit(k_means_df)  # fit the data to the visualizer
visualizer.show()  # finalize and render figure

<Axes: title={'center': 'Silhouette Score Elbow for KMeans Clustering'}, xlabel='k', ylabel='silhouette score'>


# finding optimal no. of clusters with silhouette coefficients
visualizer = SilhouetteVisualizer(KMeans(6, random_state=1))  ## Complete the code to visualize the silhouette scores for certain number of clusters
visualizer.fit(k_means_df)
visualizer.show()

<Axes: title={'center': 'Silhouette Plot of KMeans Clustering for 340 Samples in 6 Centers'}, xlabel='silhouette coefficient values', ylabel='cluster label'>


# final K-means model
kmeans = KMeans(n_clusters=6, random_state=1)  ## Complete the code to choose the number of clusters
kmeans.fit(k_means_df)

KMeans(n_clusters=6, random_state=1)

KMeans(n_clusters=6, random_state=1)


# creating a copy of the original data
df1 = df.copy()

# adding kmeans cluster labels to the original and scaled dataframes
k_means_df["KM_segments"] = kmeans.labels_
df1["KM_segments"] = kmeans.labels_


df1.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 340 entries, 0 to 339
Data columns (total 16 columns):
 #   Column                        Non-Null Count  Dtype  
---  ------                        --------------  -----  
 0   Ticker Symbol                 340 non-null    object 
 1   Security                      340 non-null    object 
 2   GICS Sector                   340 non-null    object 
 3   GICS Sub Industry             340 non-null    object 
 4   Current Price                 340 non-null    float64
 5   Price Change                  340 non-null    float64
 6   Volatility                    340 non-null    float64
 7   ROE                           340 non-null    int64  
 8   Cash Ratio                    340 non-null    int64  
 9   Net Cash Flow                 340 non-null    int64  
 10  Net Income                    340 non-null    int64  
 11  Earnings Per Share            340 non-null    float64
 12  Estimated Shares Outstanding  340 non-null    float64
 13  P/E Ratio                     340 non-null    float64
 14  P/B Ratio                     340 non-null    float64
 15  KM_segments                   340 non-null    int32  
dtypes: float64(7), int32(1), int64(4), object(4)
memory usage: 41.3+ KB


df1.columns

Index(['Ticker Symbol', 'Security', 'GICS Sector', 'GICS Sub Industry',
       'Current Price', 'Price Change', 'Volatility', 'ROE', 'Cash Ratio',
       'Net Cash Flow', 'Net Income', 'Earnings Per Share',
       'Estimated Shares Outstanding', 'P/E Ratio', 'P/B Ratio',
       'KM_segments'],
      dtype='object')


df1_num_cols=[]
df1_obj_cols=[]
for iterable in df.columns:
    if (df1.columns.dtype == 'float64' or 'int64' or 'int32'):
        df1_num_cols.append(iterable)
        # print(df1.columns.dtype == 'float64' or 'int64' or 'int32')
        print(df1.columns.dtype)
        print(df1.columns)
        # print(iterable)
        # print(df1.columns.dtype)
    else:
        df1_obj_cols.append(iterable)

object
Index(['Ticker Symbol', 'Security', 'GICS Sector', 'GICS Sub Industry',
       'Current Price', 'Price Change', 'Volatility', 'ROE', 'Cash Ratio',
       'Net Cash Flow', 'Net Income', 'Earnings Per Share',
       'Estimated Shares Outstanding', 'P/E Ratio', 'P/B Ratio',
       'KM_segments'],
      dtype='object')
object
Index(['Ticker Symbol', 'Security', 'GICS Sector', 'GICS Sub Industry',
       'Current Price', 'Price Change', 'Volatility', 'ROE', 'Cash Ratio',
       'Net Cash Flow', 'Net Income', 'Earnings Per Share',
       'Estimated Shares Outstanding', 'P/E Ratio', 'P/B Ratio',
       'KM_segments'],
      dtype='object')
object
Index(['Ticker Symbol', 'Security', 'GICS Sector', 'GICS Sub Industry',
       'Current Price', 'Price Change', 'Volatility', 'ROE', 'Cash Ratio',
       'Net Cash Flow', 'Net Income', 'Earnings Per Share',
       'Estimated Shares Outstanding', 'P/E Ratio', 'P/B Ratio',
       'KM_segments'],
      dtype='object')
object
Index(['Ticker Symbol', 'Security', 'GICS Sector', 'GICS Sub Industry',
       'Current Price', 'Price Change', 'Volatility', 'ROE', 'Cash Ratio',
       'Net Cash Flow', 'Net Income', 'Earnings Per Share',
       'Estimated Shares Outstanding', 'P/E Ratio', 'P/B Ratio',
       'KM_segments'],
      dtype='object')
object
Index(['Ticker Symbol', 'Security', 'GICS Sector', 'GICS Sub Industry',
       'Current Price', 'Price Change', 'Volatility', 'ROE', 'Cash Ratio',
       'Net Cash Flow', 'Net Income', 'Earnings Per Share',
       'Estimated Shares Outstanding', 'P/E Ratio', 'P/B Ratio',
       'KM_segments'],
      dtype='object')
object
Index(['Ticker Symbol', 'Security', 'GICS Sector', 'GICS Sub Industry',
       'Current Price', 'Price Change', 'Volatility', 'ROE', 'Cash Ratio',
       'Net Cash Flow', 'Net Income', 'Earnings Per Share',
       'Estimated Shares Outstanding', 'P/E Ratio', 'P/B Ratio',
       'KM_segments'],
      dtype='object')
object
Index(['Ticker Symbol', 'Security', 'GICS Sector', 'GICS Sub Industry',
       'Current Price', 'Price Change', 'Volatility', 'ROE', 'Cash Ratio',
       'Net Cash Flow', 'Net Income', 'Earnings Per Share',
       'Estimated Shares Outstanding', 'P/E Ratio', 'P/B Ratio',
       'KM_segments'],
      dtype='object')
object
Index(['Ticker Symbol', 'Security', 'GICS Sector', 'GICS Sub Industry',
       'Current Price', 'Price Change', 'Volatility', 'ROE', 'Cash Ratio',
       'Net Cash Flow', 'Net Income', 'Earnings Per Share',
       'Estimated Shares Outstanding', 'P/E Ratio', 'P/B Ratio',
       'KM_segments'],
      dtype='object')
object
Index(['Ticker Symbol', 'Security', 'GICS Sector', 'GICS Sub Industry',
       'Current Price', 'Price Change', 'Volatility', 'ROE', 'Cash Ratio',
       'Net Cash Flow', 'Net Income', 'Earnings Per Share',
       'Estimated Shares Outstanding', 'P/E Ratio', 'P/B Ratio',
       'KM_segments'],
      dtype='object')
object
Index(['Ticker Symbol', 'Security', 'GICS Sector', 'GICS Sub Industry',
       'Current Price', 'Price Change', 'Volatility', 'ROE', 'Cash Ratio',
       'Net Cash Flow', 'Net Income', 'Earnings Per Share',
       'Estimated Shares Outstanding', 'P/E Ratio', 'P/B Ratio',
       'KM_segments'],
      dtype='object')
object
Index(['Ticker Symbol', 'Security', 'GICS Sector', 'GICS Sub Industry',
       'Current Price', 'Price Change', 'Volatility', 'ROE', 'Cash Ratio',
       'Net Cash Flow', 'Net Income', 'Earnings Per Share',
       'Estimated Shares Outstanding', 'P/E Ratio', 'P/B Ratio',
       'KM_segments'],
      dtype='object')
object
Index(['Ticker Symbol', 'Security', 'GICS Sector', 'GICS Sub Industry',
       'Current Price', 'Price Change', 'Volatility', 'ROE', 'Cash Ratio',
       'Net Cash Flow', 'Net Income', 'Earnings Per Share',
       'Estimated Shares Outstanding', 'P/E Ratio', 'P/B Ratio',
       'KM_segments'],
      dtype='object')
object
Index(['Ticker Symbol', 'Security', 'GICS Sector', 'GICS Sub Industry',
       'Current Price', 'Price Change', 'Volatility', 'ROE', 'Cash Ratio',
       'Net Cash Flow', 'Net Income', 'Earnings Per Share',
       'Estimated Shares Outstanding', 'P/E Ratio', 'P/B Ratio',
       'KM_segments'],
      dtype='object')
object
Index(['Ticker Symbol', 'Security', 'GICS Sector', 'GICS Sub Industry',
       'Current Price', 'Price Change', 'Volatility', 'ROE', 'Cash Ratio',
       'Net Cash Flow', 'Net Income', 'Earnings Per Share',
       'Estimated Shares Outstanding', 'P/E Ratio', 'P/B Ratio',
       'KM_segments'],
      dtype='object')
object
Index(['Ticker Symbol', 'Security', 'GICS Sector', 'GICS Sub Industry',
       'Current Price', 'Price Change', 'Volatility', 'ROE', 'Cash Ratio',
       'Net Cash Flow', 'Net Income', 'Earnings Per Share',
       'Estimated Shares Outstanding', 'P/E Ratio', 'P/B Ratio',
       'KM_segments'],
      dtype='object')


df1_num_cols

['Ticker Symbol',
 'Security',
 'GICS Sector',
 'GICS Sub Industry',
 'Current Price',
 'Price Change',
 'Volatility',
 'ROE',
 'Cash Ratio',
 'Net Cash Flow',
 'Net Income',
 'Earnings Per Share',
 'Estimated Shares Outstanding',
 'P/E Ratio',
 'P/B Ratio']


df1_obj_cols

[]


df1.head(1)


df1.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 340 entries, 0 to 339
Data columns (total 16 columns):
 #   Column                        Non-Null Count  Dtype  
---  ------                        --------------  -----  
 0   Ticker Symbol                 340 non-null    object 
 1   Security                      340 non-null    object 
 2   GICS Sector                   340 non-null    object 
 3   GICS Sub Industry             340 non-null    object 
 4   Current Price                 340 non-null    float64
 5   Price Change                  340 non-null    float64
 6   Volatility                    340 non-null    float64
 7   ROE                           340 non-null    int64  
 8   Cash Ratio                    340 non-null    int64  
 9   Net Cash Flow                 340 non-null    int64  
 10  Net Income                    340 non-null    int64  
 11  Earnings Per Share            340 non-null    float64
 12  Estimated Shares Outstanding  340 non-null    float64
 13  P/E Ratio                     340 non-null    float64
 14  P/B Ratio                     340 non-null    float64
 15  KM_segments                   340 non-null    int32  
dtypes: float64(7), int32(1), int64(4), object(4)
memory usage: 41.3+ KB


df1.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 340 entries, 0 to 339
Data columns (total 16 columns):
 #   Column                        Non-Null Count  Dtype  
---  ------                        --------------  -----  
 0   Ticker Symbol                 340 non-null    object 
 1   Security                      340 non-null    object 
 2   GICS Sector                   340 non-null    object 
 3   GICS Sub Industry             340 non-null    object 
 4   Current Price                 340 non-null    float64
 5   Price Change                  340 non-null    float64
 6   Volatility                    340 non-null    float64
 7   ROE                           340 non-null    int64  
 8   Cash Ratio                    340 non-null    int64  
 9   Net Cash Flow                 340 non-null    int64  
 10  Net Income                    340 non-null    int64  
 11  Earnings Per Share            340 non-null    float64
 12  Estimated Shares Outstanding  340 non-null    float64
 13  P/E Ratio                     340 non-null    float64
 14  P/B Ratio                     340 non-null    float64
 15  KM_segments                   340 non-null    int32  
dtypes: float64(7), int32(1), int64(4), object(4)
memory usage: 41.3+ KB


df1.groupby(["KM_segments", "Ticker Symbol", "Security", "GICS Sector", "GICS Sub Industry"])

<pandas.core.groupby.generic.DataFrameGroupBy object at 0x000002CDB5755670>


df1.groupby(["KM_segments", "Ticker Symbol", "Security", "GICS Sector", "GICS Sub Industry"]).mean()


km_cluster_profile = df1.groupby(["KM_segments", "Ticker Symbol", "Security", "GICS Sector", "GICS Sub Industry"]).mean()  ## Complete the code to groupby the cluster labels


km_cluster_profile["count_in_each_segment"] = (
    df1.groupby(["KM_segments", "Ticker Symbol", "Security", "GICS Sector", "GICS Sub Industry"])["Security"].count().values  ## Complete the code to groupby the cluster labels
)


km_cluster_profile.style.highlight_max(color="lightgreen", axis=0)


## Complete the code to print the companies in each cluster
for cl in df1["KM_segments"].unique():
    print("In cluster {}, the following companies are present:".format(cl))
    print(df1[df1["KM_segments"] == cl]["Security"].unique())
    print()

In cluster 0, the following companies are present:
['American Airlines Group' 'AbbVie' 'Abbott Laboratories'
 'Archer-Daniels-Midland Co' 'Ameren Corp' 'American Electric Power'
 'AFLAC Inc' 'American International Group, Inc.'
 'Apartment Investment & Mgmt' 'Assurant Inc' 'Arthur J. Gallagher & Co.'
 'Akamai Technologies Inc' 'Albemarle Corp' 'Alaska Air Group Inc'
 'Allstate Corp' 'Allegion' 'Applied Materials Inc' 'AMETEK Inc'
 'Affiliated Managers Group Inc' 'Ameriprise Financial'
 'American Tower Corp A' 'AutoNation Inc' 'Anthem Inc.' 'Aon plc'
 'Amphenol Corp' 'Activision Blizzard' 'AvalonBay Communities, Inc.'
 'American Water Works Company Inc' 'American Express Co' 'Boeing Company'
 'Baxter International Inc.' 'BB&T Corporation' 'Bard (C.R.) Inc.'
 'BIOGEN IDEC Inc.' 'The Bank of New York Mellon Corp.' 'Ball Corp'
 'Bristol-Myers Squibb' 'Boston Scientific' 'BorgWarner'
 'Boston Properties' 'Caterpillar Inc.' 'Chubb Limited' 'CBRE Group'
 'Crown Castle International Corp.' 'Carnival Corp.'
 'CF Industries Holdings Inc' 'Citizens Financial Group' 'Church & Dwight'
 'C. H. Robinson Worldwide' 'Charter Communications' 'CIGNA Corp.'
 'Cincinnati Financial' 'Colgate-Palmolive' 'Comerica Inc.'
 'CME Group Inc.' 'Cummins Inc.' 'CMS Energy' 'Centene Corporation'
 'CenterPoint Energy' 'Capital One Financial' 'The Cooper Companies'
 'CSX Corp.' 'CenturyLink Inc' 'Cognizant Technology Solutions'
 'Citrix Systems' 'CVS Health' 'Chevron Corp.' 'Dominion Resources'
 'Delta Air Lines' 'Du Pont (E.I.)' 'Deere & Co.'
 'Discover Financial Services' 'Quest Diagnostics' 'Danaher Corp.'
 'The Walt Disney Company' 'Discovery Communications-A'
 'Discovery Communications-C' 'Delphi Automotive' 'Digital Realty Trust'
 'Dun & Bradstreet' 'Dover Corp.' 'Dr Pepper Snapple Group' 'Duke Energy'
 'DaVita Inc.' 'Ecolab Inc.' 'Consolidated Edison' 'Equifax Inc.'
 "Edison Int'l" 'Eastman Chemical' 'Equity Residential'
 'Eversource Energy' 'Essex Property Trust, Inc.' 'E*Trade'
 'Eaton Corporation' 'Entergy Corp.' 'Exelon Corp.' "Expeditors Int'l"
 'Expedia Inc.' 'Extra Space Storage' 'Fastenal Co'
 'Fortune Brands Home & Security' 'FirstEnergy Corp'
 'Fidelity National Information Services' 'Fiserv Inc' 'FLIR Systems'
 'Fluor Corp.' 'Flowserve Corporation' 'FMC Corporation'
 'Federal Realty Investment Trust' 'General Dynamics'
 'General Growth Properties Inc.' 'Corning Inc.' 'General Motors'
 'Genuine Parts' 'Garmin Ltd.' 'Goodyear Tire & Rubber'
 'Grainger (W.W.) Inc.' 'Hasbro Inc.' 'Huntington Bancshares'
 'HCA Holdings' 'Welltower Inc.' 'HCP Inc.' 'Hartford Financial Svc.Gp.'
 'Harley-Davidson' "Honeywell Int'l Inc." 'HP Inc.' 'Hormel Foods Corp.'
 'Henry Schein' 'Host Hotels & Resorts' 'The Hershey Company'
 'Humana Inc.' 'International Business Machines' 'IDEXX Laboratories'
 'Intl Flavors & Fragrances' 'International Paper' 'Interpublic Group'
 'Iron Mountain Incorporated' 'Illinois Tool Works' 'Invesco Ltd.'
 'J. B. Hunt Transport Services' 'Jacobs Engineering Group'
 'Juniper Networks' 'Kimco Realty' 'Kimberly-Clark' 'Kansas City Southern'
 'Leggett & Platt' 'Lennar Corp.' 'Laboratory Corp. of America Holding'
 'LKQ Corporation' 'L-3 Communications Holdings' 'Lilly (Eli) & Co.'
 'Lockheed Martin Corp.' 'Alliant Energy Corp' 'Leucadia National Corp.'
 'Southwest Airlines' 'Level 3 Communications' 'LyondellBasell'
 'Mastercard Inc.' 'Mid-America Apartments' 'Macerich' "Marriott Int'l."
 'Masco Corp.' 'Mattel Inc.' "Moody's Corp" 'Mondelez International'
 'MetLife Inc.' 'Mohawk Industries' 'Mead Johnson' 'McCormick & Co.'
 'Martin Marietta Materials' 'Marsh & McLennan' '3M Company'
 'Altria Group Inc' 'Marathon Petroleum' 'Merck & Co.' 'M&T Bank Corp.'
 'Mettler Toledo' 'Mylan N.V.' 'Navient' 'NASDAQ OMX Group'
 'NextEra Energy' 'Nielsen Holdings' 'Norfolk Southern Corp.'
 'Northern Trust Corp.' 'Nucor Corp.' 'Newell Brands'
 'Realty Income Corporation' 'Omnicom Group' "O'Reilly Automotive"
 "People's United Financial" 'Pitney-Bowes' 'PACCAR Inc.' 'PG&E Corp.'
 'Public Serv. Enterprise Inc.' 'PepsiCo Inc.' 'Principal Financial Group'
 'Procter & Gamble' 'Progressive Corp.' 'Pulte Homes Inc.'
 'Philip Morris International' 'PNC Financial Services' 'Pentair Ltd.'
 'Pinnacle West Capital' 'PPG Industries' 'PPL Corp.'
 'Prudential Financial' 'Phillips 66' 'Praxair Inc.' 'PayPal'
 'Ryder System' 'Royal Caribbean Cruises Ltd' 'Robert Half International'
 'Roper Industries' 'Republic Services Inc' 'SCANA Corp'
 'Charles Schwab Corporation' 'Sealed Air' 'Sherwin-Williams'
 'SL Green Realty' 'Scripps Networks Interactive Inc.' 'Southern Co.'
 'Simon Property Group Inc' 'S&P Global, Inc.' 'Stericycle Inc'
 'Sempra Energy' 'SunTrust Banks' 'State Street Corp.'
 'Synchrony Financial' 'Stryker Corp.' 'Molson Coors Brewing Company'
 'Tegna, Inc.' 'Torchmark Corp.' 'Thermo Fisher Scientific'
 'The Travelers Companies Inc.' 'Tractor Supply Company' 'Tyson Foods'
 'Tesoro Petroleum Co.' 'Total System Services' 'Texas Instruments'
 'Under Armour' 'United Continental Holdings' 'UDR Inc'
 'Universal Health Services, Inc.' 'United Health Group Inc.' 'Unum Group'
 'Union Pacific' 'United Parcel Service' 'United Technologies'
 'Varian Medical Systems' 'Valero Energy' 'Vulcan Materials'
 'Vornado Realty Trust' 'Verisk Analytics' 'Verisign Inc.' 'Ventas Inc'
 'Wec Energy Group Inc' 'Whirlpool Corp.' 'Waste Management Inc.'
 'Western Union Co' 'Weyerhaeuser Corp.' 'Wyndham Worldwide'
 'Xcel Energy Inc' 'XL Capital' 'Dentsply Sirona' 'Xerox Corp.'
 'Xylem Inc.' 'Yum! Brands Inc' 'Zimmer Biomet Holdings' 'Zions Bancorp'
 'Zoetis']

In cluster 2, the following companies are present:
['Adobe Systems Inc' 'Analog Devices, Inc.' 'Alliance Data Systems'
 'Amgen Inc' 'Broadcom' 'Bank of America Corp' 'Celgene Corp.'
 'Chipotle Mexican Grill' 'eBay Inc.' 'Equinix' 'Edwards Lifesciences'
 'Facebook' 'First Solar Inc' 'Frontier Communications' 'Halliburton Co.'
 "McDonald's Corp." 'Monster Beverage' 'Newmont Mining Corp. (Hldg. Co.)'
 'Skyworks Solutions' 'TripAdvisor' 'Vertex Pharmaceuticals Inc'
 'Waters Corporation' 'Wynn Resorts Ltd' 'Yahoo Inc.']

In cluster 3, the following companies are present:
['Alexion Pharmaceuticals' 'Amazon.com Inc' 'Intuitive Surgical Inc.'
 'Netflix Inc.' 'Priceline.com Inc' 'Regeneron']

In cluster 4, the following companies are present:
['Apache Corporation' 'Chesapeake Energy']

In cluster 5, the following companies are present:
['Anadarko Petroleum Corp' 'Arconic Inc' 'Baker Hughes Inc'
 'Cabot Oil & Gas' 'Concho Resources' 'Devon Energy Corp.' 'EOG Resources'
 'EQT Corporation' 'Freeport-McMoran Cp & Gld' 'Hess Corporation'
 'Hewlett Packard Enterprise' 'Kinder Morgan' 'The Mosaic Company'
 'Marathon Oil Corp.' 'Murphy Oil' 'Noble Energy Inc'
 'Newfield Exploration Co' 'National Oilwell Varco Inc.' 'ONEOK'
 'Occidental Petroleum' 'Quanta Services Inc.' 'Range Resources Corp.'
 'Spectra Energy Corp.' 'Southwestern Energy' 'Teradata Corp.'
 'Williams Cos.' 'Cimarex Energy']

In cluster 1, the following companies are present:
['Citigroup Inc.' 'Ford Motor' 'Gilead Sciences' 'Intel Corp.'
 'JPMorgan Chase & Co.' 'Coca Cola Company' 'Pfizer Inc.' 'AT&T Inc'
 'Verizon Communications' 'Wells Fargo' 'Exxon Mobil Corp.']


df1.groupby(["KM_segments", "GICS Sector"])['Security'].count()

KM_segments  GICS Sector                
0            Consumer Discretionary         33
             Consumer Staples               17
             Energy                          5
             Financials                     45
             Health Care                    30
             Industrials                    51
             Information Technology         20
             Materials                      17
             Real Estate                    26
             Telecommunications Services     2
             Utilities                      24
1            Consumer Discretionary          1
             Consumer Staples                1
             Energy                          1
             Financials                      3
             Health Care                     2
             Information Technology          1
             Telecommunications Services     2
2            Consumer Discretionary          4
             Consumer Staples                1
             Energy                          1
             Financials                      1
             Health Care                     5
             Information Technology          9
             Materials                       1
             Real Estate                     1
             Telecommunications Services     1
3            Consumer Discretionary          2
             Health Care                     3
             Information Technology          1
4            Energy                          2
5            Energy                         21
             Industrials                     2
             Information Technology          2
             Materials                       2
Name: Security, dtype: int64


plt.figure(figsize=(20, 20))
plt.suptitle("Boxplot of numerical variables for each cluster")

# selecting numerical columns
num_col = df.select_dtypes(include=np.number).columns.tolist()

for i, variable in enumerate(num_col):
    plt.subplot(3, 4, i + 1)
    sns.boxplot(data=df1, x="KM_segments", y=variable)

plt.tight_layout(pad=2.0)


hc_df = subset_scaled_df.copy()


hc_df


hc_df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 340 entries, 0 to 339
Data columns (total 11 columns):
 #   Column                        Non-Null Count  Dtype  
---  ------                        --------------  -----  
 0   Current Price                 340 non-null    float64
 1   Price Change                  340 non-null    float64
 2   Volatility                    340 non-null    float64
 3   ROE                           340 non-null    float64
 4   Cash Ratio                    340 non-null    float64
 5   Net Cash Flow                 340 non-null    float64
 6   Net Income                    340 non-null    float64
 7   Earnings Per Share            340 non-null    float64
 8   Estimated Shares Outstanding  340 non-null    float64
 9   P/E Ratio                     340 non-null    float64
 10  P/B Ratio                     340 non-null    float64
dtypes: float64(11)
memory usage: 29.3 KB


hc_df.isna()


len(hc_df[col])

340


type(len(hc_df[col]))

int


hc_df.columns

Index(['Current Price', 'Price Change', 'Volatility', 'ROE', 'Cash Ratio',
       'Net Cash Flow', 'Net Income', 'Earnings Per Share',
       'Estimated Shares Outstanding', 'P/E Ratio', 'P/B Ratio'],
      dtype='object')


count=0
for col in hc_df.columns:
    for i in range(len(hc_df[col])):
        if pd.isna(hc_df[col][i])==True:
            count+=1
    print("count of na's in col:", col, "is:")
    print(count)
    count=0

count of na's in col: Current Price is:
0
count of na's in col: Price Change is:
0
count of na's in col: Volatility is:
0
count of na's in col: ROE is:
0
count of na's in col: Cash Ratio is:
0
count of na's in col: Net Cash Flow is:
0
count of na's in col: Net Income is:
0
count of na's in col: Earnings Per Share is:
0
count of na's in col: Estimated Shares Outstanding is:
0
count of na's in col: P/E Ratio is:
0
count of na's in col: P/B Ratio is:
0


len(hc_df)

340


# list of distance metrics

# https://docs.scipy.org/doc/scipy/reference/spatial.distance.html

# excluded `"jensenshannon", ` due to: 
# error: ValueError: The condensed distance matrix must contain only finite values.
# https://github.com/SegataLab/hclust2/issues/3
# https://github.com/CoderSales/unsupervised-learning-clustering/issues/7


distance_metrics = ["braycurtis", "canberra", "chebyshev", "cityblock", "correlation", "cosine", "euclidean", "mahalanobis", "minkowski", "seuclidean", "sqeuclidean"] ## Complete the code to add distance metrics

# list of linkage methods

# https://docs.scipy.org/doc/scipy/reference/generated/scipy.cluster.hierarchy.linkage.html

# 'ward' excluded due to error:
# ValueError: Method 'ward' requires the distance metric to be Euclidean

linkage_methods = ["single", "complete", "average", "weighted"] ## Complete the code to add linkages

# distance_metrics = ["braycurtis", "canberra", "chebyshev", "cityblock", "correlation", "cosine", "euclidean", "jensenshannon", "mahalanobis", "minkowski", "seuclidean", "sqeuclidean"] ## Complete the code to add distance metrics
# linkage_methods = ["single", "complete", "average", "weighted"] ## Complete the code to add linkages
# gave error:
# ValueError: The condensed distance matrix must contain only finite values.

high_cophenet_corr = 0
high_dm_lm = [0, 0]

for dm in distance_metrics:
    for lm in linkage_methods:
        Z = linkage(hc_df, metric=dm, method=lm)
        c, coph_dists = cophenet(Z, pdist(hc_df))
        print(
            "Cophenetic correlation for {} distance and {} linkage is {}.".format(
                dm.capitalize(), lm, c
            )
        )
        if high_cophenet_corr < c:
            high_cophenet_corr = c
            high_dm_lm[0] = dm
            high_dm_lm[1] = lm
            
# printing the combination of distance metric and linkage method with the highest cophenetic correlation
print('*'*100)
print(
    "Highest cophenetic correlation is {}, which is obtained with {} distance and {} linkage.".format(
        high_cophenet_corr, high_dm_lm[0].capitalize(), high_dm_lm[1]
    )
)

Cophenetic correlation for Braycurtis distance and single linkage is 0.5212071810658095.
Cophenetic correlation for Braycurtis distance and complete linkage is 0.10240430426791322.
Cophenetic correlation for Braycurtis distance and average linkage is 0.32627435525839565.
Cophenetic correlation for Braycurtis distance and weighted linkage is 0.20642920853912072.
Cophenetic correlation for Canberra distance and single linkage is 0.3154284565587037.
Cophenetic correlation for Canberra distance and complete linkage is 0.12703984768895782.
Cophenetic correlation for Canberra distance and average linkage is 0.28553906567883447.
Cophenetic correlation for Canberra distance and weighted linkage is 0.1626519582860221.
Cophenetic correlation for Chebyshev distance and single linkage is 0.9062538164750717.
Cophenetic correlation for Chebyshev distance and complete linkage is 0.598891419111242.
Cophenetic correlation for Chebyshev distance and average linkage is 0.9338265528030499.
Cophenetic correlation for Chebyshev distance and weighted linkage is 0.9127355892367.
Cophenetic correlation for Cityblock distance and single linkage is 0.9334186366528574.
Cophenetic correlation for Cityblock distance and complete linkage is 0.7375328863205818.
Cophenetic correlation for Cityblock distance and average linkage is 0.9302145048594667.
Cophenetic correlation for Cityblock distance and weighted linkage is 0.731045513520281.
Cophenetic correlation for Correlation distance and single linkage is 0.02168228519550047.
Cophenetic correlation for Correlation distance and complete linkage is 0.13026598376888868.
Cophenetic correlation for Correlation distance and average linkage is 0.23298451399122191.
Cophenetic correlation for Correlation distance and weighted linkage is 0.1763411190029438.
Cophenetic correlation for Cosine distance and single linkage is 0.1633637841831045.
Cophenetic correlation for Cosine distance and complete linkage is 0.14558585821013348.
Cophenetic correlation for Cosine distance and average linkage is 0.27565476401031014.
Cophenetic correlation for Cosine distance and weighted linkage is 0.24177997570119666.
Cophenetic correlation for Euclidean distance and single linkage is 0.9232271494002922.
Cophenetic correlation for Euclidean distance and complete linkage is 0.7873280186580672.
Cophenetic correlation for Euclidean distance and average linkage is 0.9422540609560814.
Cophenetic correlation for Euclidean distance and weighted linkage is 0.8693784298129404.
Cophenetic correlation for Mahalanobis distance and single linkage is 0.9259195530524591.
Cophenetic correlation for Mahalanobis distance and complete linkage is 0.792530720285.
Cophenetic correlation for Mahalanobis distance and average linkage is 0.9247324030159737.
Cophenetic correlation for Mahalanobis distance and weighted linkage is 0.8708317490180426.
Cophenetic correlation for Minkowski distance and single linkage is 0.9232271494002922.
Cophenetic correlation for Minkowski distance and complete linkage is 0.7873280186580672.
Cophenetic correlation for Minkowski distance and average linkage is 0.9422540609560814.
Cophenetic correlation for Minkowski distance and weighted linkage is 0.8693784298129404.
Cophenetic correlation for Seuclidean distance and single linkage is 0.923227149400292.
Cophenetic correlation for Seuclidean distance and complete linkage is 0.787328018658067.
Cophenetic correlation for Seuclidean distance and average linkage is 0.9422540609560816.
Cophenetic correlation for Seuclidean distance and weighted linkage is 0.8693784298129404.
Cophenetic correlation for Sqeuclidean distance and single linkage is 0.815145614432825.
Cophenetic correlation for Sqeuclidean distance and complete linkage is 0.7784062963343734.
Cophenetic correlation for Sqeuclidean distance and average linkage is 0.8984640112163556.
Cophenetic correlation for Sqeuclidean distance and weighted linkage is 0.5506923587865349.
****************************************************************************************************
Highest cophenetic correlation is 0.9422540609560816, which is obtained with Seuclidean distance and average linkage.


high_cophenet_corr

0.9422540609560816


high_dm_lm[0]

'seuclidean'


high_dm_lm[1]

'average'


# list of linkage methods
linkage_methods = ["single", "complete", "average", "weighted"] ## Complete the code to add linkages

high_cophenet_corr = 0
high_dm_lm = [0, 0]

for lm in linkage_methods:
    Z = linkage(hc_df, metric="euclidean", method=lm)
    c, coph_dists = cophenet(Z, pdist(hc_df))
    print("Cophenetic correlation for {} linkage is {}.".format(lm, c))
    if high_cophenet_corr < c:
        high_cophenet_corr = c
        high_dm_lm[0] = "euclidean"
        high_dm_lm[1] = lm
        
# printing the combination of distance metric and linkage method with the highest cophenetic correlation
print('*'*100)
print(
    "Highest cophenetic correlation is {}, which is obtained with {} linkage.".format(
        high_cophenet_corr, high_dm_lm[1]
    )
)

Cophenetic correlation for single linkage is 0.9232271494002922.
Cophenetic correlation for complete linkage is 0.7873280186580672.
Cophenetic correlation for average linkage is 0.9422540609560814.
Cophenetic correlation for weighted linkage is 0.8693784298129404.
****************************************************************************************************
Highest cophenetic correlation is 0.9422540609560814, which is obtained with average linkage.


# list of linkage methods
linkage_methods = ["single", "complete", "average", "centroid", "ward", "weighted"] ## Complete the code to add linkages

# lists to save results of cophenetic correlation calculation
compare_cols = ["Linkage", "Cophenetic Coefficient"]
compare = []

# to create a subplot image
fig, axs = plt.subplots(len(linkage_methods), 1, figsize=(15, 30))

# We will enumerate through the list of linkage methods above
# For each linkage method, we will plot the dendrogram and calculate the cophenetic correlation
for i, method in enumerate(linkage_methods):
    Z = linkage(hc_df, metric="euclidean", method=method)

    dendrogram(Z, ax=axs[i])
    axs[i].set_title(f"Dendrogram ({method.capitalize()} Linkage)")

    coph_corr, coph_dist = cophenet(Z, pdist(hc_df))
    axs[i].annotate(
        f"Cophenetic\nCorrelation\n{coph_corr:0.2f}",
        (0.80, 0.80),
        xycoords="axes fraction",
    )

    compare.append([method, coph_corr])


# create and print a dataframe to compare cophenetic correlations for different linkage methods
df_cc = pd.DataFrame(compare, columns=compare_cols)
df_cc = df_cc.sort_values(by="Cophenetic Coefficient")
df_cc


HCmodel = AgglomerativeClustering(n_clusters=1, affinity='euclidean', linkage='ward')  ## Complete the code to define the hierarchical clustering model
HCmodel.fit(hc_df)

AgglomerativeClustering(affinity='euclidean', n_clusters=1)

AgglomerativeClustering(affinity='euclidean', n_clusters=1)


# creating a copy of the original data
df2 = df.copy()

# adding hierarchical cluster labels to the original and scaled dataframes
hc_df["HC_segments"] = HCmodel.labels_
df2["HC_segments"] = HCmodel.labels_


df2.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 340 entries, 0 to 339
Data columns (total 16 columns):
 #   Column                        Non-Null Count  Dtype  
---  ------                        --------------  -----  
 0   Ticker Symbol                 340 non-null    object 
 1   Security                      340 non-null    object 
 2   GICS Sector                   340 non-null    object 
 3   GICS Sub Industry             340 non-null    object 
 4   Current Price                 340 non-null    float64
 5   Price Change                  340 non-null    float64
 6   Volatility                    340 non-null    float64
 7   ROE                           340 non-null    int64  
 8   Cash Ratio                    340 non-null    int64  
 9   Net Cash Flow                 340 non-null    int64  
 10  Net Income                    340 non-null    int64  
 11  Earnings Per Share            340 non-null    float64
 12  Estimated Shares Outstanding  340 non-null    float64
 13  P/E Ratio                     340 non-null    float64
 14  P/B Ratio                     340 non-null    float64
 15  HC_segments                   340 non-null    int64  
dtypes: float64(7), int64(5), object(4)
memory usage: 42.6+ KB


hc_cluster_profile = df2.groupby(["HC_segments", "Ticker Symbol", "Security", "GICS Sector", "GICS Sub Industry"]).mean()  ## Complete the code to groupby the cluster labels


object_list

['Ticker Symbol', 'Security', 'GICS Sector', 'GICS Sub Industry']


# hc_cluster_profile["count_in_each_segment"] = (
#     df2.groupby(["HC_segments", "Ticker Symbol", "Security", "GICS Sector", "GICS Sub Industry"])["Security"].count().values  ## Complete the code to groupby the cluster labels
# )


hc_cluster_profile["count_in_each_segment"] = (
    df2.groupby(["HC_segments", 'Ticker Symbol', 'Security', 'GICS Sector', "GICS Sub Industry"])["GICS Sub Industry"].count().values  ## Complete the code to groupby the cluster labels
)


# from ChatGPT3:

# Requests:
#  how to loop through dataframe which displays max highlighted in green so that output of all this stock exchange ticker data like pe ratio and pb ratio etc does not have to display like a thousand rows but rather only those rows with a max value in them
# regenerate code for all rows as there are more than pe and pb ratios
# exclude dtype obj cols
# adapt given that currently : hc_cluster_profile.style.highlight_max(color="lightgreen", axis=0)

# response test 1:

# function to highlight maximum values in numeric columns
# def highlight_max(s):
#     if np.issubdtype(s.dtype, np.number):
#         is_max = s == s.max()
#         return ['background-color: green' if v else '' for v in is_max]
#     else:
#         return ['' for _ in s]

# # highlight maximum values in numeric columns only
# numeric_cols = hc_cluster_profile.select_dtypes(include=np.number).columns.tolist()
# hc_cluster_profile.style.apply(highlight_max, subset=numeric_cols)

# filter

# above failed as whole dataframe printed

# response test 2:

# function to highlight maximum values in numeric columns
def highlight_max(s):
    if np.issubdtype(s.dtype, np.number):
        is_max = s == s.max()
        return ['background-color: green' if v else '' for v in is_max]
    else:
        return ['' for _ in s]

# highlight maximum values in numeric columns only
numeric_cols = hc_cluster_profile.select_dtypes(include=np.number).columns.tolist()
hc_cluster_profile.style.apply(highlight_max, subset=numeric_cols)

# filter DataFrame to show only rows with max value in any numeric column
max_values = hc_cluster_profile.loc[hc_cluster_profile[numeric_cols].idxmax().values]

# display filtered DataFrame
print("Rows with maximum values in any numeric column:")
print(max_values)

Rows with maximum values in any numeric column:
                                                                                                                Current Price   
HC_segments Ticker Symbol Security                GICS Sector            GICS Sub Industry                                      
0           PCLN          Priceline.com Inc       Consumer Discretionary Internet & Direct Marketing Retail       1274.949951  \
            FSLR          First Solar Inc         Information Technology Semiconductors                             65.989998   
            SWN           Southwestern Energy     Energy                 Oil & Gas Exploration & Production          7.110000   
            APA           Apache Corporation      Energy                 Oil & Gas Exploration & Production         44.470001   
            FB            Facebook                Information Technology Internet Software & Services              104.660004   
            BAC           Bank of America Corp    Financials             Banks                                      16.830000   
            JPM           JPMorgan Chase & Co.    Financials             Banks                                      66.029999   
            PCLN          Priceline.com Inc       Consumer Discretionary Internet & Direct Marketing Retail       1274.949951   
            PFE           Pfizer Inc.             Health Care            Pharmaceuticals                            32.279999   
            AMZN          Amazon.com Inc          Consumer Discretionary Internet & Direct Marketing Retail        675.890015   
            ADS           Alliance Data Systems   Information Technology Data Processing & Outsourced Services     276.570007   
            AAL           American Airlines Group Industrials            Airlines                                   42.349998   

                                                                                                                Price Change   
HC_segments Ticker Symbol Security                GICS Sector            GICS Sub Industry                                     
0           PCLN          Priceline.com Inc       Consumer Discretionary Internet & Direct Marketing Retail         3.190527  \
            FSLR          First Solar Inc         Information Technology Semiconductors                            55.051683   
            SWN           Southwestern Energy     Energy                 Oil & Gas Exploration & Production       -44.798137   
            APA           Apache Corporation      Energy                 Oil & Gas Exploration & Production        11.397804   
            FB            Facebook                Information Technology Internet Software & Services              16.224320   
            BAC           Bank of America Corp    Financials             Banks                                      8.440722   
            JPM           JPMorgan Chase & Co.    Financials             Banks                                      8.033377   
            PCLN          Priceline.com Inc       Consumer Discretionary Internet & Direct Marketing Retail         3.190527   
            PFE           Pfizer Inc.             Health Care            Pharmaceuticals                            3.130991   
            AMZN          Amazon.com Inc          Consumer Discretionary Internet & Direct Marketing Retail        32.268105   
            ADS           Alliance Data Systems   Information Technology Data Processing & Outsourced Services      6.189286   
            AAL           American Airlines Group Industrials            Airlines                                   9.999995   

                                                                                                                Volatility   
HC_segments Ticker Symbol Security                GICS Sector            GICS Sub Industry                                   
0           PCLN          Priceline.com Inc       Consumer Discretionary Internet & Direct Marketing Retail       1.268340  \
            FSLR          First Solar Inc         Information Technology Semiconductors                           2.075216   
            SWN           Southwestern Energy     Energy                 Oil & Gas Exploration & Production       4.580042   
            APA           Apache Corporation      Energy                 Oil & Gas Exploration & Production       2.405408   
            FB            Facebook                Information Technology Internet Software & Services             1.320606   
            BAC           Bank of America Corp    Financials             Banks                                    1.418688   
            JPM           JPMorgan Chase & Co.    Financials             Banks                                    1.130337   
            PCLN          Priceline.com Inc       Consumer Discretionary Internet & Direct Marketing Retail       1.268340   
            PFE           Pfizer Inc.             Health Care            Pharmaceuticals                          1.238748   
            AMZN          Amazon.com Inc          Consumer Discretionary Internet & Direct Marketing Retail       1.460386   
            ADS           Alliance Data Systems   Information Technology Data Processing & Outsourced Services    1.116976   
            AAL           American Airlines Group Industrials            Airlines                                 1.687151   

                                                                                                                  ROE   
HC_segments Ticker Symbol Security                GICS Sector            GICS Sub Industry                              
0           PCLN          Priceline.com Inc       Consumer Discretionary Internet & Direct Marketing Retail      29.0  \
            FSLR          First Solar Inc         Information Technology Semiconductors                          10.0   
            SWN           Southwestern Energy     Energy                 Oil & Gas Exploration & Production     200.0   
            APA           Apache Corporation      Energy                 Oil & Gas Exploration & Production     917.0   
            FB            Facebook                Information Technology Internet Software & Services             8.0   
            BAC           Bank of America Corp    Financials             Banks                                    6.0   
            JPM           JPMorgan Chase & Co.    Financials             Banks                                   10.0   
            PCLN          Priceline.com Inc       Consumer Discretionary Internet & Direct Marketing Retail      29.0   
            PFE           Pfizer Inc.             Health Care            Pharmaceuticals                         11.0   
            AMZN          Amazon.com Inc          Consumer Discretionary Internet & Direct Marketing Retail       4.0   
            ADS           Alliance Data Systems   Information Technology Data Processing & Outsourced Services   30.0   
            AAL           American Airlines Group Industrials            Airlines                               135.0   

                                                                                                                Cash Ratio   
HC_segments Ticker Symbol Security                GICS Sector            GICS Sub Industry                                   
0           PCLN          Priceline.com Inc       Consumer Discretionary Internet & Direct Marketing Retail          184.0  \
            FSLR          First Solar Inc         Information Technology Semiconductors                              190.0   
            SWN           Southwestern Energy     Energy                 Oil & Gas Exploration & Production            2.0   
            APA           Apache Corporation      Energy                 Oil & Gas Exploration & Production           80.0   
            FB            Facebook                Information Technology Internet Software & Services                958.0   
            BAC           Bank of America Corp    Financials             Banks                                        99.0   
            JPM           JPMorgan Chase & Co.    Financials             Banks                                        99.0   
            PCLN          Priceline.com Inc       Consumer Discretionary Internet & Direct Marketing Retail          184.0   
            PFE           Pfizer Inc.             Health Care            Pharmaceuticals                              79.0   
            AMZN          Amazon.com Inc          Consumer Discretionary Internet & Direct Marketing Retail           58.0   
            ADS           Alliance Data Systems   Information Technology Data Processing & Outsourced Services        25.0   
            AAL           American Airlines Group Industrials            Airlines                                     51.0   

                                                                                                                Net Cash Flow   
HC_segments Ticker Symbol Security                GICS Sector            GICS Sub Industry                                      
0           PCLN          Priceline.com Inc       Consumer Discretionary Internet & Direct Marketing Retail     -1.671386e+09  \
            FSLR          First Solar Inc         Information Technology Semiconductors                         -3.552280e+08   
            SWN           Southwestern Energy     Energy                 Oil & Gas Exploration & Production     -3.800000e+07   
            APA           Apache Corporation      Energy                 Oil & Gas Exploration & Production      6.980000e+08   
            FB            Facebook                Information Technology Internet Software & Services            5.920000e+08   
            BAC           Bank of America Corp    Financials             Banks                                   2.076400e+10   
            JPM           JPMorgan Chase & Co.    Financials             Banks                                  -7.341000e+09   
            PCLN          Priceline.com Inc       Consumer Discretionary Internet & Direct Marketing Retail     -1.671386e+09   
            PFE           Pfizer Inc.             Health Care            Pharmaceuticals                         2.980000e+08   
            AMZN          Amazon.com Inc          Consumer Discretionary Internet & Direct Marketing Retail      1.333000e+09   
            ADS           Alliance Data Systems   Information Technology Data Processing & Outsourced Services   9.088500e+07   
            AAL           American Airlines Group Industrials            Airlines                               -6.040000e+08   

                                                                                                                  Net Income   
HC_segments Ticker Symbol Security                GICS Sector            GICS Sub Industry                                     
0           PCLN          Priceline.com Inc       Consumer Discretionary Internet & Direct Marketing Retail     2.551360e+09  \
            FSLR          First Solar Inc         Information Technology Semiconductors                         5.464210e+08   
            SWN           Southwestern Energy     Energy                 Oil & Gas Exploration & Production    -4.556000e+09   
            APA           Apache Corporation      Energy                 Oil & Gas Exploration & Production    -2.352800e+10   
            FB            Facebook                Information Technology Internet Software & Services           3.669000e+09   
            BAC           Bank of America Corp    Financials             Banks                                  1.588800e+10   
            JPM           JPMorgan Chase & Co.    Financials             Banks                                  2.444200e+10   
            PCLN          Priceline.com Inc       Consumer Discretionary Internet & Direct Marketing Retail     2.551360e+09   
            PFE           Pfizer Inc.             Health Care            Pharmaceuticals                        6.960000e+09   
            AMZN          Amazon.com Inc          Consumer Discretionary Internet & Direct Marketing Retail     5.960000e+08   
            ADS           Alliance Data Systems   Information Technology Data Processing & Outsourced Services  5.965410e+08   
            AAL           American Airlines Group Industrials            Airlines                               7.610000e+09   

                                                                                                                Earnings Per Share   
HC_segments Ticker Symbol Security                GICS Sector            GICS Sub Industry                                           
0           PCLN          Priceline.com Inc       Consumer Discretionary Internet & Direct Marketing Retail                  50.09  \
            FSLR          First Solar Inc         Information Technology Semiconductors                                       5.42   
            SWN           Southwestern Energy     Energy                 Oil & Gas Exploration & Production                  -6.07   
            APA           Apache Corporation      Energy                 Oil & Gas Exploration & Production                 -61.20   
            FB            Facebook                Information Technology Internet Software & Services                         1.31   
            BAC           Bank of America Corp    Financials             Banks                                                4.18   
            JPM           JPMorgan Chase & Co.    Financials             Banks                                                6.05   
            PCLN          Priceline.com Inc       Consumer Discretionary Internet & Direct Marketing Retail                  50.09   
            PFE           Pfizer Inc.             Health Care            Pharmaceuticals                                      1.13   
            AMZN          Amazon.com Inc          Consumer Discretionary Internet & Direct Marketing Retail                   1.28   
            ADS           Alliance Data Systems   Information Technology Data Processing & Outsourced Services                8.91   
            AAL           American Airlines Group Industrials            Airlines                                            11.39   

                                                                                                                Estimated Shares Outstanding   
HC_segments Ticker Symbol Security                GICS Sector            GICS Sub Industry                                                     
0           PCLN          Priceline.com Inc       Consumer Discretionary Internet & Direct Marketing Retail                     5.093552e+07  \
            FSLR          First Solar Inc         Information Technology Semiconductors                                         1.008157e+08   
            SWN           Southwestern Energy     Energy                 Oil & Gas Exploration & Production                     4.021417e+08   
            APA           Apache Corporation      Energy                 Oil & Gas Exploration & Production                     3.844444e+08   
            FB            Facebook                Information Technology Internet Software & Services                           2.800763e+09   
            BAC           Bank of America Corp    Financials             Banks                                                  8.450695e+08   
            JPM           JPMorgan Chase & Co.    Financials             Banks                                                  4.040000e+09   
            PCLN          Priceline.com Inc       Consumer Discretionary Internet & Direct Marketing Retail                     5.093552e+07   
            PFE           Pfizer Inc.             Health Care            Pharmaceuticals                                        6.159292e+09   
            AMZN          Amazon.com Inc          Consumer Discretionary Internet & Direct Marketing Retail                     4.656250e+08   
            ADS           Alliance Data Systems   Information Technology Data Processing & Outsourced Services                  6.695185e+07   
            AAL           American Airlines Group Industrials            Airlines                                               6.681299e+08   

                                                                                                                 P/E Ratio   
HC_segments Ticker Symbol Security                GICS Sector            GICS Sub Industry                                   
0           PCLN          Priceline.com Inc       Consumer Discretionary Internet & Direct Marketing Retail      25.453183  \
            FSLR          First Solar Inc         Information Technology Semiconductors                          12.175276   
            SWN           Southwestern Energy     Energy                 Oil & Gas Exploration & Production      93.089287   
            APA           Apache Corporation      Energy                 Oil & Gas Exploration & Production      93.089287   
            FB            Facebook                Information Technology Internet Software & Services            79.893133   
            BAC           Bank of America Corp    Financials             Banks                                   13.004785   
            JPM           JPMorgan Chase & Co.    Financials             Banks                                   10.914049   
            PCLN          Priceline.com Inc       Consumer Discretionary Internet & Direct Marketing Retail      25.453183   
            PFE           Pfizer Inc.             Health Care            Pharmaceuticals                         28.566371   
            AMZN          Amazon.com Inc          Consumer Discretionary Internet & Direct Marketing Retail     528.039074   
            ADS           Alliance Data Systems   Information Technology Data Processing & Outsourced Services   31.040405   
            AAL           American Airlines Group Industrials            Airlines                                 3.718174   

                                                                                                                 P/B Ratio   
HC_segments Ticker Symbol Security                GICS Sector            GICS Sub Industry                                   
0           PCLN          Priceline.com Inc       Consumer Discretionary Internet & Direct Marketing Retail      -1.052429  \
            FSLR          First Solar Inc         Information Technology Semiconductors                          22.563791   
            SWN           Southwestern Energy     Energy                 Oil & Gas Exploration & Production       1.273530   
            APA           Apache Corporation      Energy                 Oil & Gas Exploration & Production       4.970809   
            FB            Facebook                Information Technology Internet Software & Services             5.884467   
            BAC           Bank of America Corp    Financials             Banks                                   -0.938007   
            JPM           JPMorgan Chase & Co.    Financials             Banks                                   -1.886881   
            PCLN          Priceline.com Inc       Consumer Discretionary Internet & Direct Marketing Retail      -1.052429   
            PFE           Pfizer Inc.             Health Care            Pharmaceuticals                         -4.213309   
            AMZN          Amazon.com Inc          Consumer Discretionary Internet & Direct Marketing Retail       3.904430   
            ADS           Alliance Data Systems   Information Technology Data Processing & Outsourced Services  129.064585   
            AAL           American Airlines Group Industrials            Airlines                                -8.784219   

                                                                                                                count_in_each_segment  
HC_segments Ticker Symbol Security                GICS Sector            GICS Sub Industry                                             
0           PCLN          Priceline.com Inc       Consumer Discretionary Internet & Direct Marketing Retail                         1  
            FSLR          First Solar Inc         Information Technology Semiconductors                                             1  
            SWN           Southwestern Energy     Energy                 Oil & Gas Exploration & Production                         1  
            APA           Apache Corporation      Energy                 Oil & Gas Exploration & Production                         1  
            FB            Facebook                Information Technology Internet Software & Services                               1  
            BAC           Bank of America Corp    Financials             Banks                                                      1  
            JPM           JPMorgan Chase & Co.    Financials             Banks                                                      1  
            PCLN          Priceline.com Inc       Consumer Discretionary Internet & Direct Marketing Retail                         1  
            PFE           Pfizer Inc.             Health Care            Pharmaceuticals                                            1  
            AMZN          Amazon.com Inc          Consumer Discretionary Internet & Direct Marketing Retail                         1  
            ADS           Alliance Data Systems   Information Technology Data Processing & Outsourced Services                      1  
            AAL           American Airlines Group Industrials            Airlines                                                   1


hc_cluster_profile.style.highlight_max(color="lightgreen", axis=0)


max_df=hc_cluster_profile.style.highlight_max(color="lightgreen", axis=0)


type(max_df)

pandas.io.formats.style.Styler


max_df_df=max_df.data
for col in max_df_df.columns:
    
    # for i in max_df[col]:
    print(col)

Current Price
Price Change
Volatility
ROE
Cash Ratio
Net Cash Flow
Net Income
Earnings Per Share
Estimated Shares Outstanding
P/E Ratio
P/B Ratio
count_in_each_segment


type(max_df_df)

pandas.core.frame.DataFrame


# TODO
# Try to get max of each column and print it.


max=max_df_df.loc[max_df_df[col].idxmax()]

max

Current Price                   4.235000e+01
Price Change                    9.999995e+00
Volatility                      1.687151e+00
ROE                             1.350000e+02
Cash Ratio                      5.100000e+01
Net Cash Flow                  -6.040000e+08
Net Income                      7.610000e+09
Earnings Per Share              1.139000e+01
Estimated Shares Outstanding    6.681299e+08
P/E Ratio                       3.718174e+00
P/B Ratio                      -8.784219e+00
count_in_each_segment           1.000000e+00
Name: (0, AAL, American Airlines Group, Industrials, Airlines), dtype: float64


index_of_max=max_df_df[col].idxmax()


index_of_max

(0, 'AAL', 'American Airlines Group', 'Industrials', 'Airlines')


max=max_df_df.loc[max_df_df[col].idxmax()]
index_of_max=max_df_df[col].idxmax()


max_df_df.head(5)


for index,col in enumerate(max_df_df.columns):
    print(col)
    # print(max_df_df[col].idxmax())
    print('index',index)
    print('col',col)
    # print(max_df_df[col][index])

Current Price
index 0
col Current Price
Price Change
index 1
col Price Change
Volatility
index 2
col Volatility
ROE
index 3
col ROE
Cash Ratio
index 4
col Cash Ratio
Net Cash Flow
index 5
col Net Cash Flow
Net Income
index 6
col Net Income
Earnings Per Share
index 7
col Earnings Per Share
Estimated Shares Outstanding
index 8
col Estimated Shares Outstanding
P/E Ratio
index 9
col P/E Ratio
P/B Ratio
index 10
col P/B Ratio
count_in_each_segment
index 11
col count_in_each_segment


    # max=max_df_df.loc[max_df_df[col].idxmax()]
    # index_of_max=max_df_df[col].idxmax()
    # max_df_df[col].idxmax()
    # for i,val in enumerate(max_df_df[col]):


        # if max_df_df[col][i] == max:
        # if max_df_df[col][i] == 42.349998:
        # print(max_df_df[col][i])
        # print(max_df_df[col][i])
        # print(val)


## Complete the code to print the companies in each cluster
for cl in df2['Ticker Symbol'].unique():
    print("In cluster {}, the following companies are present:".format(cl))
    print(df2[df2['Ticker Symbol'] == cl]["Security"].unique())
    print()

In cluster AAL, the following companies are present:
['American Airlines Group']

In cluster ABBV, the following companies are present:
['AbbVie']

In cluster ABT, the following companies are present:
['Abbott Laboratories']

In cluster ADBE, the following companies are present:
['Adobe Systems Inc']

In cluster ADI, the following companies are present:
['Analog Devices, Inc.']

In cluster ADM, the following companies are present:
['Archer-Daniels-Midland Co']

In cluster ADS, the following companies are present:
['Alliance Data Systems']

In cluster AEE, the following companies are present:
['Ameren Corp']

In cluster AEP, the following companies are present:
['American Electric Power']

In cluster AFL, the following companies are present:
['AFLAC Inc']

In cluster AIG, the following companies are present:
['American International Group, Inc.']

In cluster AIV, the following companies are present:
['Apartment Investment & Mgmt']

In cluster AIZ, the following companies are present:
['Assurant Inc']

In cluster AJG, the following companies are present:
['Arthur J. Gallagher & Co.']

In cluster AKAM, the following companies are present:
['Akamai Technologies Inc']

In cluster ALB, the following companies are present:
['Albemarle Corp']

In cluster ALK, the following companies are present:
['Alaska Air Group Inc']

In cluster ALL, the following companies are present:
['Allstate Corp']

In cluster ALLE, the following companies are present:
['Allegion']

In cluster ALXN, the following companies are present:
['Alexion Pharmaceuticals']

In cluster AMAT, the following companies are present:
['Applied Materials Inc']

In cluster AME, the following companies are present:
['AMETEK Inc']

In cluster AMG, the following companies are present:
['Affiliated Managers Group Inc']

In cluster AMGN, the following companies are present:
['Amgen Inc']

In cluster AMP, the following companies are present:
['Ameriprise Financial']

In cluster AMT, the following companies are present:
['American Tower Corp A']

In cluster AMZN, the following companies are present:
['Amazon.com Inc']

In cluster AN, the following companies are present:
['AutoNation Inc']

In cluster ANTM, the following companies are present:
['Anthem Inc.']

In cluster AON, the following companies are present:
['Aon plc']

In cluster APA, the following companies are present:
['Apache Corporation']

In cluster APC, the following companies are present:
['Anadarko Petroleum Corp']

In cluster APH, the following companies are present:
['Amphenol Corp']

In cluster ARNC, the following companies are present:
['Arconic Inc']

In cluster ATVI, the following companies are present:
['Activision Blizzard']

In cluster AVB, the following companies are present:
['AvalonBay Communities, Inc.']

In cluster AVGO, the following companies are present:
['Broadcom']

In cluster AWK, the following companies are present:
['American Water Works Company Inc']

In cluster AXP, the following companies are present:
['American Express Co']

In cluster BA, the following companies are present:
['Boeing Company']

In cluster BAC, the following companies are present:
['Bank of America Corp']

In cluster BAX, the following companies are present:
['Baxter International Inc.']

In cluster BBT, the following companies are present:
['BB&T Corporation']

In cluster BCR, the following companies are present:
['Bard (C.R.) Inc.']

In cluster BHI, the following companies are present:
['Baker Hughes Inc']

In cluster BIIB, the following companies are present:
['BIOGEN IDEC Inc.']

In cluster BK, the following companies are present:
['The Bank of New York Mellon Corp.']

In cluster BLL, the following companies are present:
['Ball Corp']

In cluster BMY, the following companies are present:
['Bristol-Myers Squibb']

In cluster BSX, the following companies are present:
['Boston Scientific']

In cluster BWA, the following companies are present:
['BorgWarner']

In cluster BXP, the following companies are present:
['Boston Properties']

In cluster C, the following companies are present:
['Citigroup Inc.']

In cluster CAT, the following companies are present:
['Caterpillar Inc.']

In cluster CB, the following companies are present:
['Chubb Limited']

In cluster CBG, the following companies are present:
['CBRE Group']

In cluster CCI, the following companies are present:
['Crown Castle International Corp.']

In cluster CCL, the following companies are present:
['Carnival Corp.']

In cluster CELG, the following companies are present:
['Celgene Corp.']

In cluster CF, the following companies are present:
['CF Industries Holdings Inc']

In cluster CFG, the following companies are present:
['Citizens Financial Group']

In cluster CHD, the following companies are present:
['Church & Dwight']

In cluster CHK, the following companies are present:
['Chesapeake Energy']

In cluster CHRW, the following companies are present:
['C. H. Robinson Worldwide']

In cluster CHTR, the following companies are present:
['Charter Communications']

In cluster CI, the following companies are present:
['CIGNA Corp.']

In cluster CINF, the following companies are present:
['Cincinnati Financial']

In cluster CL, the following companies are present:
['Colgate-Palmolive']

In cluster CMA, the following companies are present:
['Comerica Inc.']

In cluster CME, the following companies are present:
['CME Group Inc.']

In cluster CMG, the following companies are present:
['Chipotle Mexican Grill']

In cluster CMI, the following companies are present:
['Cummins Inc.']

In cluster CMS, the following companies are present:
['CMS Energy']

In cluster CNC, the following companies are present:
['Centene Corporation']

In cluster CNP, the following companies are present:
['CenterPoint Energy']

In cluster COF, the following companies are present:
['Capital One Financial']

In cluster COG, the following companies are present:
['Cabot Oil & Gas']

In cluster COO, the following companies are present:
['The Cooper Companies']

In cluster CSX, the following companies are present:
['CSX Corp.']

In cluster CTL, the following companies are present:
['CenturyLink Inc']

In cluster CTSH, the following companies are present:
['Cognizant Technology Solutions']

In cluster CTXS, the following companies are present:
['Citrix Systems']

In cluster CVS, the following companies are present:
['CVS Health']

In cluster CVX, the following companies are present:
['Chevron Corp.']

In cluster CXO, the following companies are present:
['Concho Resources']

In cluster D, the following companies are present:
['Dominion Resources']

In cluster DAL, the following companies are present:
['Delta Air Lines']

In cluster DD, the following companies are present:
['Du Pont (E.I.)']

In cluster DE, the following companies are present:
['Deere & Co.']

In cluster DFS, the following companies are present:
['Discover Financial Services']

In cluster DGX, the following companies are present:
['Quest Diagnostics']

In cluster DHR, the following companies are present:
['Danaher Corp.']

In cluster DIS, the following companies are present:
['The Walt Disney Company']

In cluster DISCA, the following companies are present:
['Discovery Communications-A']

In cluster DISCK, the following companies are present:
['Discovery Communications-C']

In cluster DLPH, the following companies are present:
['Delphi Automotive']

In cluster DLR, the following companies are present:
['Digital Realty Trust']

In cluster DNB, the following companies are present:
['Dun & Bradstreet']

In cluster DOV, the following companies are present:
['Dover Corp.']

In cluster DPS, the following companies are present:
['Dr Pepper Snapple Group']

In cluster DUK, the following companies are present:
['Duke Energy']

In cluster DVA, the following companies are present:
['DaVita Inc.']

In cluster DVN, the following companies are present:
['Devon Energy Corp.']

In cluster EBAY, the following companies are present:
['eBay Inc.']

In cluster ECL, the following companies are present:
['Ecolab Inc.']

In cluster ED, the following companies are present:
['Consolidated Edison']

In cluster EFX, the following companies are present:
['Equifax Inc.']

In cluster EIX, the following companies are present:
["Edison Int'l"]

In cluster EMN, the following companies are present:
['Eastman Chemical']

In cluster EOG, the following companies are present:
['EOG Resources']

In cluster EQIX, the following companies are present:
['Equinix']

In cluster EQR, the following companies are present:
['Equity Residential']

In cluster EQT, the following companies are present:
['EQT Corporation']

In cluster ES, the following companies are present:
['Eversource Energy']

In cluster ESS, the following companies are present:
['Essex Property Trust, Inc.']

In cluster ETFC, the following companies are present:
['E*Trade']

In cluster ETN, the following companies are present:
['Eaton Corporation']

In cluster ETR, the following companies are present:
['Entergy Corp.']

In cluster EW, the following companies are present:
['Edwards Lifesciences']

In cluster EXC, the following companies are present:
['Exelon Corp.']

In cluster EXPD, the following companies are present:
["Expeditors Int'l"]

In cluster EXPE, the following companies are present:
['Expedia Inc.']

In cluster EXR, the following companies are present:
['Extra Space Storage']

In cluster F, the following companies are present:
['Ford Motor']

In cluster FAST, the following companies are present:
['Fastenal Co']

In cluster FB, the following companies are present:
['Facebook']

In cluster FBHS, the following companies are present:
['Fortune Brands Home & Security']

In cluster FCX, the following companies are present:
['Freeport-McMoran Cp & Gld']

In cluster FE, the following companies are present:
['FirstEnergy Corp']

In cluster FIS, the following companies are present:
['Fidelity National Information Services']

In cluster FISV, the following companies are present:
['Fiserv Inc']

In cluster FLIR, the following companies are present:
['FLIR Systems']

In cluster FLR, the following companies are present:
['Fluor Corp.']

In cluster FLS, the following companies are present:
['Flowserve Corporation']

In cluster FMC, the following companies are present:
['FMC Corporation']

In cluster FRT, the following companies are present:
['Federal Realty Investment Trust']

In cluster FSLR, the following companies are present:
['First Solar Inc']

In cluster FTR, the following companies are present:
['Frontier Communications']

In cluster GD, the following companies are present:
['General Dynamics']

In cluster GGP, the following companies are present:
['General Growth Properties Inc.']

In cluster GILD, the following companies are present:
['Gilead Sciences']

In cluster GLW, the following companies are present:
['Corning Inc.']

In cluster GM, the following companies are present:
['General Motors']

In cluster GPC, the following companies are present:
['Genuine Parts']

In cluster GRMN, the following companies are present:
['Garmin Ltd.']

In cluster GT, the following companies are present:
['Goodyear Tire & Rubber']

In cluster GWW, the following companies are present:
['Grainger (W.W.) Inc.']

In cluster HAL, the following companies are present:
['Halliburton Co.']

In cluster HAS, the following companies are present:
['Hasbro Inc.']

In cluster HBAN, the following companies are present:
['Huntington Bancshares']

In cluster HCA, the following companies are present:
['HCA Holdings']

In cluster HCN, the following companies are present:
['Welltower Inc.']

In cluster HCP, the following companies are present:
['HCP Inc.']

In cluster HES, the following companies are present:
['Hess Corporation']

In cluster HIG, the following companies are present:
['Hartford Financial Svc.Gp.']

In cluster HOG, the following companies are present:
['Harley-Davidson']

In cluster HON, the following companies are present:
["Honeywell Int'l Inc."]

In cluster HPE, the following companies are present:
['Hewlett Packard Enterprise']

In cluster HPQ, the following companies are present:
['HP Inc.']

In cluster HRL, the following companies are present:
['Hormel Foods Corp.']

In cluster HSIC, the following companies are present:
['Henry Schein']

In cluster HST, the following companies are present:
['Host Hotels & Resorts']

In cluster HSY, the following companies are present:
['The Hershey Company']

In cluster HUM, the following companies are present:
['Humana Inc.']

In cluster IBM, the following companies are present:
['International Business Machines']

In cluster IDXX, the following companies are present:
['IDEXX Laboratories']

In cluster IFF, the following companies are present:
['Intl Flavors & Fragrances']

In cluster INTC, the following companies are present:
['Intel Corp.']

In cluster IP, the following companies are present:
['International Paper']

In cluster IPG, the following companies are present:
['Interpublic Group']

In cluster IRM, the following companies are present:
['Iron Mountain Incorporated']

In cluster ISRG, the following companies are present:
['Intuitive Surgical Inc.']

In cluster ITW, the following companies are present:
['Illinois Tool Works']

In cluster IVZ, the following companies are present:
['Invesco Ltd.']

In cluster JBHT, the following companies are present:
['J. B. Hunt Transport Services']

In cluster JEC, the following companies are present:
['Jacobs Engineering Group']

In cluster JNPR, the following companies are present:
['Juniper Networks']

In cluster JPM, the following companies are present:
['JPMorgan Chase & Co.']

In cluster KIM, the following companies are present:
['Kimco Realty']

In cluster KMB, the following companies are present:
['Kimberly-Clark']

In cluster KMI, the following companies are present:
['Kinder Morgan']

In cluster KO, the following companies are present:
['Coca Cola Company']

In cluster KSU, the following companies are present:
['Kansas City Southern']

In cluster LEG, the following companies are present:
['Leggett & Platt']

In cluster LEN, the following companies are present:
['Lennar Corp.']

In cluster LH, the following companies are present:
['Laboratory Corp. of America Holding']

In cluster LKQ, the following companies are present:
['LKQ Corporation']

In cluster LLL, the following companies are present:
['L-3 Communications Holdings']

In cluster LLY, the following companies are present:
['Lilly (Eli) & Co.']

In cluster LMT, the following companies are present:
['Lockheed Martin Corp.']

In cluster LNT, the following companies are present:
['Alliant Energy Corp']

In cluster LUK, the following companies are present:
['Leucadia National Corp.']

In cluster LUV, the following companies are present:
['Southwest Airlines']

In cluster LVLT, the following companies are present:
['Level 3 Communications']

In cluster LYB, the following companies are present:
['LyondellBasell']

In cluster MA, the following companies are present:
['Mastercard Inc.']

In cluster MAA, the following companies are present:
['Mid-America Apartments']

In cluster MAC, the following companies are present:
['Macerich']

In cluster MAR, the following companies are present:
["Marriott Int'l."]

In cluster MAS, the following companies are present:
['Masco Corp.']

In cluster MAT, the following companies are present:
['Mattel Inc.']

In cluster MCD, the following companies are present:
["McDonald's Corp."]

In cluster MCO, the following companies are present:
["Moody's Corp"]

In cluster MDLZ, the following companies are present:
['Mondelez International']

In cluster MET, the following companies are present:
['MetLife Inc.']

In cluster MHK, the following companies are present:
['Mohawk Industries']

In cluster MJN, the following companies are present:
['Mead Johnson']

In cluster MKC, the following companies are present:
['McCormick & Co.']

In cluster MLM, the following companies are present:
['Martin Marietta Materials']

In cluster MMC, the following companies are present:
['Marsh & McLennan']

In cluster MMM, the following companies are present:
['3M Company']

In cluster MNST, the following companies are present:
['Monster Beverage']

In cluster MO, the following companies are present:
['Altria Group Inc']

In cluster MOS, the following companies are present:
['The Mosaic Company']

In cluster MPC, the following companies are present:
['Marathon Petroleum']

In cluster MRK, the following companies are present:
['Merck & Co.']

In cluster MRO, the following companies are present:
['Marathon Oil Corp.']

In cluster MTB, the following companies are present:
['M&T Bank Corp.']

In cluster MTD, the following companies are present:
['Mettler Toledo']

In cluster MUR, the following companies are present:
['Murphy Oil']

In cluster MYL, the following companies are present:
['Mylan N.V.']

In cluster NAVI, the following companies are present:
['Navient']

In cluster NBL, the following companies are present:
['Noble Energy Inc']

In cluster NDAQ, the following companies are present:
['NASDAQ OMX Group']

In cluster NEE, the following companies are present:
['NextEra Energy']

In cluster NEM, the following companies are present:
['Newmont Mining Corp. (Hldg. Co.)']

In cluster NFLX, the following companies are present:
['Netflix Inc.']

In cluster NFX, the following companies are present:
['Newfield Exploration Co']

In cluster NLSN, the following companies are present:
['Nielsen Holdings']

In cluster NOV, the following companies are present:
['National Oilwell Varco Inc.']

In cluster NSC, the following companies are present:
['Norfolk Southern Corp.']

In cluster NTRS, the following companies are present:
['Northern Trust Corp.']

In cluster NUE, the following companies are present:
['Nucor Corp.']

In cluster NWL, the following companies are present:
['Newell Brands']

In cluster O, the following companies are present:
['Realty Income Corporation']

In cluster OKE, the following companies are present:
['ONEOK']

In cluster OMC, the following companies are present:
['Omnicom Group']

In cluster ORLY, the following companies are present:
["O'Reilly Automotive"]

In cluster OXY, the following companies are present:
['Occidental Petroleum']

In cluster PBCT, the following companies are present:
["People's United Financial"]

In cluster PBI, the following companies are present:
['Pitney-Bowes']

In cluster PCAR, the following companies are present:
['PACCAR Inc.']

In cluster PCG, the following companies are present:
['PG&E Corp.']

In cluster PCLN, the following companies are present:
['Priceline.com Inc']

In cluster PEG, the following companies are present:
['Public Serv. Enterprise Inc.']

In cluster PEP, the following companies are present:
['PepsiCo Inc.']

In cluster PFE, the following companies are present:
['Pfizer Inc.']

In cluster PFG, the following companies are present:
['Principal Financial Group']

In cluster PG, the following companies are present:
['Procter & Gamble']

In cluster PGR, the following companies are present:
['Progressive Corp.']

In cluster PHM, the following companies are present:
['Pulte Homes Inc.']

In cluster PM, the following companies are present:
['Philip Morris International']

In cluster PNC, the following companies are present:
['PNC Financial Services']

In cluster PNR, the following companies are present:
['Pentair Ltd.']

In cluster PNW, the following companies are present:
['Pinnacle West Capital']

In cluster PPG, the following companies are present:
['PPG Industries']

In cluster PPL, the following companies are present:
['PPL Corp.']

In cluster PRU, the following companies are present:
['Prudential Financial']

In cluster PSX, the following companies are present:
['Phillips 66']

In cluster PWR, the following companies are present:
['Quanta Services Inc.']

In cluster PX, the following companies are present:
['Praxair Inc.']

In cluster PYPL, the following companies are present:
['PayPal']

In cluster R, the following companies are present:
['Ryder System']

In cluster RCL, the following companies are present:
['Royal Caribbean Cruises Ltd']

In cluster REGN, the following companies are present:
['Regeneron']

In cluster RHI, the following companies are present:
['Robert Half International']

In cluster ROP, the following companies are present:
['Roper Industries']

In cluster RRC, the following companies are present:
['Range Resources Corp.']

In cluster RSG, the following companies are present:
['Republic Services Inc']

In cluster SCG, the following companies are present:
['SCANA Corp']

In cluster SCHW, the following companies are present:
['Charles Schwab Corporation']

In cluster SE, the following companies are present:
['Spectra Energy Corp.']

In cluster SEE, the following companies are present:
['Sealed Air']

In cluster SHW, the following companies are present:
['Sherwin-Williams']

In cluster SLG, the following companies are present:
['SL Green Realty']

In cluster SNI, the following companies are present:
['Scripps Networks Interactive Inc.']

In cluster SO, the following companies are present:
['Southern Co.']

In cluster SPG, the following companies are present:
['Simon Property Group Inc']

In cluster SPGI, the following companies are present:
['S&P Global, Inc.']

In cluster SRCL, the following companies are present:
['Stericycle Inc']

In cluster SRE, the following companies are present:
['Sempra Energy']

In cluster STI, the following companies are present:
['SunTrust Banks']

In cluster STT, the following companies are present:
['State Street Corp.']

In cluster SWKS, the following companies are present:
['Skyworks Solutions']

In cluster SWN, the following companies are present:
['Southwestern Energy']

In cluster SYF, the following companies are present:
['Synchrony Financial']

In cluster SYK, the following companies are present:
['Stryker Corp.']

In cluster T, the following companies are present:
['AT&T Inc']

In cluster TAP, the following companies are present:
['Molson Coors Brewing Company']

In cluster TDC, the following companies are present:
['Teradata Corp.']

In cluster TGNA, the following companies are present:
['Tegna, Inc.']

In cluster TMK, the following companies are present:
['Torchmark Corp.']

In cluster TMO, the following companies are present:
['Thermo Fisher Scientific']

In cluster TRIP, the following companies are present:
['TripAdvisor']

In cluster TRV, the following companies are present:
['The Travelers Companies Inc.']

In cluster TSCO, the following companies are present:
['Tractor Supply Company']

In cluster TSN, the following companies are present:
['Tyson Foods']

In cluster TSO, the following companies are present:
['Tesoro Petroleum Co.']

In cluster TSS, the following companies are present:
['Total System Services']

In cluster TXN, the following companies are present:
['Texas Instruments']

In cluster UAA, the following companies are present:
['Under Armour']

In cluster UAL, the following companies are present:
['United Continental Holdings']

In cluster UDR, the following companies are present:
['UDR Inc']

In cluster UHS, the following companies are present:
['Universal Health Services, Inc.']

In cluster UNH, the following companies are present:
['United Health Group Inc.']

In cluster UNM, the following companies are present:
['Unum Group']

In cluster UNP, the following companies are present:
['Union Pacific']

In cluster UPS, the following companies are present:
['United Parcel Service']

In cluster UTX, the following companies are present:
['United Technologies']

In cluster VAR, the following companies are present:
['Varian Medical Systems']

In cluster VLO, the following companies are present:
['Valero Energy']

In cluster VMC, the following companies are present:
['Vulcan Materials']

In cluster VNO, the following companies are present:
['Vornado Realty Trust']

In cluster VRSK, the following companies are present:
['Verisk Analytics']

In cluster VRSN, the following companies are present:
['Verisign Inc.']

In cluster VRTX, the following companies are present:
['Vertex Pharmaceuticals Inc']

In cluster VTR, the following companies are present:
['Ventas Inc']

In cluster VZ, the following companies are present:
['Verizon Communications']

In cluster WAT, the following companies are present:
['Waters Corporation']

In cluster WEC, the following companies are present:
['Wec Energy Group Inc']

In cluster WFC, the following companies are present:
['Wells Fargo']

In cluster WHR, the following companies are present:
['Whirlpool Corp.']

In cluster WM, the following companies are present:
['Waste Management Inc.']

In cluster WMB, the following companies are present:
['Williams Cos.']

In cluster WU, the following companies are present:
['Western Union Co']

In cluster WY, the following companies are present:
['Weyerhaeuser Corp.']

In cluster WYN, the following companies are present:
['Wyndham Worldwide']

In cluster WYNN, the following companies are present:
['Wynn Resorts Ltd']

In cluster XEC, the following companies are present:
['Cimarex Energy']

In cluster XEL, the following companies are present:
['Xcel Energy Inc']

In cluster XL, the following companies are present:
['XL Capital']

In cluster XOM, the following companies are present:
['Exxon Mobil Corp.']

In cluster XRAY, the following companies are present:
['Dentsply Sirona']

In cluster XRX, the following companies are present:
['Xerox Corp.']

In cluster XYL, the following companies are present:
['Xylem Inc.']

In cluster YHOO, the following companies are present:
['Yahoo Inc.']

In cluster YUM, the following companies are present:
['Yum! Brands Inc']

In cluster ZBH, the following companies are present:
['Zimmer Biomet Holdings']

In cluster ZION, the following companies are present:
['Zions Bancorp']

In cluster ZTS, the following companies are present:
['Zoetis']


## Failed Attempt to group by df2['GICS Sub Industry'].unique()
## Duplicated from previous cell:
## Complete the code to print the companies in each cluster
for cl in df2['GICS Sub Industry'].unique():
    print("In cluster {}, the following companies are present:".format(cl))
    print(df2[df2['GICS Sub Industry'] == cl]["Security"].unique())
    print()

In cluster Airlines, the following companies are present:
['American Airlines Group' 'Alaska Air Group Inc' 'Delta Air Lines'
 'Southwest Airlines' 'United Continental Holdings']

In cluster Pharmaceuticals, the following companies are present:
['AbbVie' 'Lilly (Eli) & Co.' 'Merck & Co.' 'Mylan N.V.' 'Pfizer Inc.'
 'Zoetis']

In cluster Health Care Equipment, the following companies are present:
['Abbott Laboratories' 'Baxter International Inc.' 'Bard (C.R.) Inc.'
 'Boston Scientific' 'Edwards Lifesciences' 'IDEXX Laboratories'
 'Intuitive Surgical Inc.' 'Stryker Corp.' 'Thermo Fisher Scientific'
 'Varian Medical Systems' 'Zimmer Biomet Holdings']

In cluster Application Software, the following companies are present:
['Adobe Systems Inc' 'Teradata Corp.']

In cluster Semiconductors, the following companies are present:
['Analog Devices, Inc.' 'Broadcom' 'First Solar Inc' 'Intel Corp.'
 'Skyworks Solutions' 'Texas Instruments']

In cluster Agricultural Products, the following companies are present:
['Archer-Daniels-Midland Co']

In cluster Data Processing & Outsourced Services, the following companies are present:
['Alliance Data Systems' 'PayPal']

In cluster MultiUtilities, the following companies are present:
['Ameren Corp' 'CMS Energy' 'CenterPoint Energy' 'Eversource Energy'
 'Exelon Corp.' 'NextEra Energy' 'PG&E Corp.' 'Pinnacle West Capital'
 'SCANA Corp' 'Sempra Energy' 'Xcel Energy Inc']

In cluster Electric Utilities, the following companies are present:
['American Electric Power' 'Dominion Resources' 'Duke Energy'
 'Consolidated Edison' "Edison Int'l" 'Entergy Corp.' 'FirstEnergy Corp'
 'Alliant Energy Corp' 'Public Serv. Enterprise Inc.' 'PPL Corp.'
 'Southern Co.' 'Wec Energy Group Inc']

In cluster Life & Health Insurance, the following companies are present:
['AFLAC Inc' 'MetLife Inc.' 'Torchmark Corp.']

In cluster Property & Casualty Insurance, the following companies are present:
['American International Group, Inc.' 'Allstate Corp' 'Chubb Limited'
 'Cincinnati Financial' 'Hartford Financial Svc.Gp.' 'Progressive Corp.'
 'The Travelers Companies Inc.' 'XL Capital']

In cluster REITs, the following companies are present:
['Apartment Investment & Mgmt' 'Boston Properties'
 'Crown Castle International Corp.' 'Equinix' 'Equity Residential'
 'Welltower Inc.' 'HCP Inc.' 'Host Hotels & Resorts'
 'Iron Mountain Incorporated' 'Kimco Realty' 'Simon Property Group Inc'
 'Vornado Realty Trust' 'Ventas Inc' 'Weyerhaeuser Corp.']

In cluster Multi-line Insurance, the following companies are present:
['Assurant Inc']

In cluster Insurance Brokers, the following companies are present:
['Arthur J. Gallagher & Co.' 'Aon plc' 'Marsh & McLennan']

In cluster Internet Software & Services, the following companies are present:
['Akamai Technologies Inc' 'Citrix Systems' 'eBay Inc.' 'Facebook'
 'Fidelity National Information Services' 'Fiserv Inc' 'Mastercard Inc.'
 'Netflix Inc.' 'Total System Services' 'Verisign Inc.' 'Western Union Co'
 'Yahoo Inc.']

In cluster Specialty Chemicals, the following companies are present:
['Albemarle Corp' 'Ecolab Inc.' 'Intl Flavors & Fragrances'
 'Sherwin-Williams']

In cluster Building Products, the following companies are present:
['Allegion' 'Fastenal Co' 'Fortune Brands Home & Security' 'Masco Corp.']

In cluster Biotechnology, the following companies are present:
['Alexion Pharmaceuticals' 'Amgen Inc' 'BIOGEN IDEC Inc.' 'Celgene Corp.'
 'Gilead Sciences' 'Regeneron' 'Vertex Pharmaceuticals Inc']

In cluster Semiconductor Equipment, the following companies are present:
['Applied Materials Inc']

In cluster Electrical Components & Equipment, the following companies are present:
['AMETEK Inc']

In cluster Asset Management & Custody Banks, the following companies are present:
['Affiliated Managers Group Inc' 'Ameriprise Financial' 'Invesco Ltd.'
 'Northern Trust Corp.']

In cluster Specialized REITs, the following companies are present:
['American Tower Corp A' 'Digital Realty Trust' 'Extra Space Storage']

In cluster Internet & Direct Marketing Retail, the following companies are present:
['Amazon.com Inc' 'Expedia Inc.' 'Priceline.com Inc' 'TripAdvisor']

In cluster Specialty Stores, the following companies are present:
['AutoNation Inc' 'Genuine Parts' "O'Reilly Automotive"]

In cluster Managed Health Care, the following companies are present:
['Anthem Inc.' 'CIGNA Corp.' 'Centene Corporation' 'Humana Inc.'
 'United Health Group Inc.']

In cluster Oil & Gas Exploration & Production, the following companies are present:
['Apache Corporation' 'Anadarko Petroleum Corp' 'Cabot Oil & Gas'
 'Concho Resources' 'Devon Energy Corp.' 'EOG Resources' 'EQT Corporation'
 'Marathon Oil Corp.' 'Noble Energy Inc' 'Newfield Exploration Co' 'ONEOK'
 'Occidental Petroleum' 'Range Resources Corp.' 'Southwestern Energy'
 'Williams Cos.' 'Cimarex Energy']

In cluster Electronic Components, the following companies are present:
['Amphenol Corp' 'Corning Inc.']

In cluster Aerospace & Defense, the following companies are present:
['Arconic Inc' 'Boeing Company' 'General Dynamics' 'Lockheed Martin Corp.']

In cluster Home Entertainment Software, the following companies are present:
['Activision Blizzard']

In cluster Residential REITs, the following companies are present:
['AvalonBay Communities, Inc.' 'Essex Property Trust, Inc.'
 'Mid-America Apartments' 'UDR Inc']

In cluster Water Utilities, the following companies are present:
['American Water Works Company Inc']

In cluster Consumer Finance, the following companies are present:
['American Express Co' 'Capital One Financial'
 'Discover Financial Services' 'Navient' 'Synchrony Financial']

In cluster Banks, the following companies are present:
['Bank of America Corp' 'BB&T Corporation'
 'The Bank of New York Mellon Corp.' 'Citigroup Inc.'
 'Huntington Bancshares' 'JPMorgan Chase & Co.' 'M&T Bank Corp.'
 'PNC Financial Services' 'SunTrust Banks' 'Wells Fargo']

In cluster Oil & Gas Equipment & Services, the following companies are present:
['Baker Hughes Inc' 'Halliburton Co.' 'National Oilwell Varco Inc.']

In cluster Metal & Glass Containers, the following companies are present:
['Ball Corp']

In cluster Health Care Distributors, the following companies are present:
['Bristol-Myers Squibb' 'Henry Schein' 'Waters Corporation']

In cluster Auto Parts & Equipment, the following companies are present:
['BorgWarner' 'Delphi Automotive']

In cluster Construction & Farm Machinery & Heavy Trucks, the following companies are present:
['Caterpillar Inc.' 'Deere & Co.' 'PACCAR Inc.']

In cluster Real Estate Services, the following companies are present:
['CBRE Group']

In cluster Hotels, Resorts & Cruise Lines, the following companies are present:
['Carnival Corp.' "Marriott Int'l." 'Royal Caribbean Cruises Ltd'
 'Wyndham Worldwide']

In cluster Fertilizers & Agricultural Chemicals, the following companies are present:
['CF Industries Holdings Inc' 'The Mosaic Company']

In cluster Regional Banks, the following companies are present:
['Citizens Financial Group' 'Comerica Inc.' 'Zions Bancorp']

In cluster Household Products, the following companies are present:
['Church & Dwight' 'Colgate-Palmolive' 'Kimberly-Clark']

In cluster Integrated Oil & Gas, the following companies are present:
['Chesapeake Energy' 'Chevron Corp.' 'Hess Corporation' 'Murphy Oil'
 'Exxon Mobil Corp.']

In cluster Air Freight & Logistics, the following companies are present:
['C. H. Robinson Worldwide' "Expeditors Int'l" 'United Parcel Service']

In cluster Cable & Satellite, the following companies are present:
['Charter Communications' 'Discovery Communications-A'
 'Discovery Communications-C']

In cluster Financial Exchanges & Data, the following companies are present:
['CME Group Inc.']

In cluster Restaurants, the following companies are present:
['Chipotle Mexican Grill' "McDonald's Corp." 'Yum! Brands Inc']

In cluster Industrial Machinery, the following companies are present:
['Cummins Inc.' 'Dover Corp.' 'Flowserve Corporation'
 'Illinois Tool Works' 'Pentair Ltd.']

In cluster Health Care Supplies, the following companies are present:
['The Cooper Companies' 'Dentsply Sirona']

In cluster Railroads, the following companies are present:
['CSX Corp.' 'Kansas City Southern' 'Norfolk Southern Corp.'
 'Union Pacific']

In cluster Integrated Telecommunications Services, the following companies are present:
['CenturyLink Inc' 'Frontier Communications' 'AT&T Inc'
 'Verizon Communications']

In cluster IT Consulting & Other Services, the following companies are present:
['Cognizant Technology Solutions' 'International Business Machines'
 'Xerox Corp.']

In cluster Drug Retail, the following companies are present:
['CVS Health']

In cluster Diversified Chemicals, the following companies are present:
['Du Pont (E.I.)' 'Eastman Chemical' 'FMC Corporation' 'LyondellBasell'
 'PPG Industries']

In cluster Health Care Facilities, the following companies are present:
['Quest Diagnostics' 'DaVita Inc.' 'HCA Holdings'
 'Laboratory Corp. of America Holding' 'Universal Health Services, Inc.']

In cluster Industrial Conglomerates, the following companies are present:
['Danaher Corp.' 'Eaton Corporation' "Honeywell Int'l Inc."
 'Jacobs Engineering Group' 'Leggett & Platt'
 'L-3 Communications Holdings' '3M Company' 'Quanta Services Inc.'
 'Ryder System' 'Roper Industries' 'Republic Services Inc'
 'Stericycle Inc' 'United Technologies' 'Xylem Inc.']

In cluster Broadcasting & Cable TV, the following companies are present:
['The Walt Disney Company' 'Scripps Networks Interactive Inc.']

In cluster Research & Consulting Services, the following companies are present:
['Dun & Bradstreet' 'Equifax Inc.' 'Nielsen Holdings' 'Verisk Analytics']

In cluster Soft Drinks, the following companies are present:
['Dr Pepper Snapple Group' 'Coca Cola Company' 'Monster Beverage'
 'PepsiCo Inc.']

In cluster Investment Banking & Brokerage, the following companies are present:
['E*Trade' 'Charles Schwab Corporation']

In cluster Automobile Manufacturers, the following companies are present:
['Ford Motor' 'General Motors']

In cluster Copper, the following companies are present:
['Freeport-McMoran Cp & Gld']

In cluster Electronic Equipment & Instruments, the following companies are present:
['FLIR Systems']

In cluster Diversified Commercial Services, the following companies are present:
['Fluor Corp.']

In cluster Retail REITs, the following companies are present:
['Federal Realty Investment Trust' 'General Growth Properties Inc.'
 'Macerich' 'Realty Income Corporation']

In cluster Consumer Electronics, the following companies are present:
['Garmin Ltd.']

In cluster Tires & Rubber, the following companies are present:
['Goodyear Tire & Rubber']

In cluster Industrial Materials, the following companies are present:
['Grainger (W.W.) Inc.']

In cluster Leisure Products, the following companies are present:
['Hasbro Inc.' 'Mattel Inc.']

In cluster Motorcycle Manufacturers, the following companies are present:
['Harley-Davidson']

In cluster Technology Hardware, Storage & Peripherals, the following companies are present:
['Hewlett Packard Enterprise']

In cluster Computer Hardware, the following companies are present:
['HP Inc.']

In cluster Packaged Foods & Meats, the following companies are present:
['Hormel Foods Corp.' 'The Hershey Company' 'Mondelez International'
 'Mead Johnson' 'McCormick & Co.' 'Tyson Foods']

In cluster Paper Packaging, the following companies are present:
['International Paper' 'Sealed Air']

In cluster Advertising, the following companies are present:
['Interpublic Group' 'Omnicom Group']

In cluster Trucking, the following companies are present:
['J. B. Hunt Transport Services']

In cluster Networking Equipment, the following companies are present:
['Juniper Networks']

In cluster Oil & Gas Refining & Marketing & Transportation, the following companies are present:
['Kinder Morgan' 'Marathon Petroleum' 'Phillips 66' 'Spectra Energy Corp.'
 'Tesoro Petroleum Co.' 'Valero Energy']

In cluster Homebuilding, the following companies are present:
['Lennar Corp.' 'Pulte Homes Inc.']

In cluster Distributors, the following companies are present:
['LKQ Corporation']

In cluster Multi-Sector Holdings, the following companies are present:
['Leucadia National Corp.']

In cluster Alternative Carriers, the following companies are present:
['Level 3 Communications']

In cluster Diversified Financial Services, the following companies are present:
["Moody's Corp" 'NASDAQ OMX Group' 'Principal Financial Group'
 'Prudential Financial' 'S&P Global, Inc.' 'State Street Corp.'
 'Unum Group']

In cluster Home Furnishings, the following companies are present:
['Mohawk Industries']

In cluster Construction Materials, the following companies are present:
['Martin Marietta Materials' 'Vulcan Materials']

In cluster Tobacco, the following companies are present:
['Altria Group Inc' 'Philip Morris International']

In cluster Life Sciences Tools & Services, the following companies are present:
['Mettler Toledo']

In cluster Gold, the following companies are present:
['Newmont Mining Corp. (Hldg. Co.)']

In cluster Steel, the following companies are present:
['Nucor Corp.']

In cluster Housewares & Specialties, the following companies are present:
['Newell Brands']

In cluster Thrifts & Mortgage Finance, the following companies are present:
["People's United Financial"]

In cluster Technology, Hardware, Software and Supplies, the following companies are present:
['Pitney-Bowes']

In cluster Personal Products, the following companies are present:
['Procter & Gamble']

In cluster Industrial Gases, the following companies are present:
['Praxair Inc.']

In cluster Human Resource & Employment Services, the following companies are present:
['Robert Half International']

In cluster Office REITs, the following companies are present:
['SL Green Realty']

In cluster Brewers, the following companies are present:
['Molson Coors Brewing Company']

In cluster Publishing, the following companies are present:
['Tegna, Inc.']

In cluster Specialty Retail, the following companies are present:
['Tractor Supply Company']

In cluster Apparel, Accessories & Luxury Goods, the following companies are present:
['Under Armour']

In cluster Household Appliances, the following companies are present:
['Whirlpool Corp.']

In cluster Environmental Services, the following companies are present:
['Waste Management Inc.']

In cluster Casinos & Gaming, the following companies are present:
['Wynn Resorts Ltd']


df2.groupby(["HC_segments", "GICS Sector"])['Security'].count()

HC_segments  GICS Sector                
0            Consumer Discretionary         40
             Consumer Staples               19
             Energy                         30
             Financials                     49
             Health Care                    40
             Industrials                    53
             Information Technology         33
             Materials                      20
             Real Estate                    27
             Telecommunications Services     5
             Utilities                      24
Name: Security, dtype: int64


plt.gcf().number

1

<Figure size 800x550 with 0 Axes>


plt.get_fignums()

[]


allaxes = fig.get_axes()


allaxes

[<Axes: title={'center': 'Dendrogram (Single Linkage)'}>,
 <Axes: title={'center': 'Dendrogram (Complete Linkage)'}>,
 <Axes: title={'center': 'Dendrogram (Average Linkage)'}>,
 <Axes: title={'center': 'Dendrogram (Centroid Linkage)'}>,
 <Axes: title={'center': 'Dendrogram (Ward Linkage)'}>,
 <Axes: title={'center': 'Dendrogram (Weighted Linkage)'}>]


# unused:
# https://stackoverflow.com/questions/49572582/distplot-got-an-unexpected-keyword-argument-figsize
# Solves figsize:
# https://stackoverflow.com/questions/31594549/how-to-change-the-figure-size-of-a-seaborn-axes-or-figure-level-plot
# plt.figure(figsize=(3,3))
# ax=plt.subplot(111, title='title')
# for item in ([ax.title, ax.xaxis.label, ax.yaxis.label] +
#              ax.get_xticklabels() + ax.get_yticklabels()):
#     item.set_fontsize(9)
# plt.subplot(figsize=(4,4))


# ax=plt.subplot(title='title')
# for item in ([ax.title, ax.xaxis.label, ax.yaxis.label] +
#              ax.get_xticklabels() + ax.get_yticklabels()):
#     item.set_fontsize(20)


plt.figure(figsize=(30, 8))
plt.suptitle("Boxplot of numerical variables for each cluster")

for i, variable in enumerate(num_col):
    plt.subplot(1, 12, i + 1)
    ax=sns.boxplot(data=df2, x="HC_segments", y=variable)
    for item in ([ax.title, ax.xaxis.label, ax.yaxis.label] +
                ax.get_xticklabels() + ax.get_yticklabels()):
        item.set_fontsize(15)

plt.tight_layout(pad=2.0)
plt.gcf().number

1

	count	unique	top	freq	mean	std	min	25%	50%	75%	max
Ticker Symbol	340	340	AAL	1	NaN	NaN	NaN	NaN	NaN	NaN	NaN
Security	340	340	American Airlines Group	1	NaN	NaN	NaN	NaN	NaN	NaN	NaN
GICS Sector	340	11	Industrials	53	NaN	NaN	NaN	NaN	NaN	NaN	NaN
GICS Sub Industry	340	104	Oil & Gas Exploration & Production	16	NaN	NaN	NaN	NaN	NaN	NaN	NaN
Current Price	340.0	NaN	NaN	NaN	80.862345	98.055086	4.5	38.555	59.705	92.880001	1274.949951
Price Change	340.0	NaN	NaN	NaN	4.078194	12.006338	-47.129693	-0.939484	4.819505	10.695493	55.051683
Volatility	340.0	NaN	NaN	NaN	1.525976	0.591798	0.733163	1.134878	1.385593	1.695549	4.580042
ROE	340.0	NaN	NaN	NaN	39.597059	96.547538	1.0	9.75	15.0	27.0	917.0
Cash Ratio	340.0	NaN	NaN	NaN	70.023529	90.421331	0.0	18.0	47.0	99.0	958.0
Net Cash Flow	340.0	NaN	NaN	NaN	55537620.588235	1946365312.175789	-11208000000.0	-193906500.0	2098000.0	169810750.0	20764000000.0
Net Income	340.0	NaN	NaN	NaN	1494384602.941176	3940150279.327936	-23528000000.0	352301250.0	707336000.0	1899000000.0	24442000000.0
Earnings Per Share	340.0	NaN	NaN	NaN	2.776662	6.587779	-61.2	1.5575	2.895	4.62	50.09
Estimated Shares Outstanding	340.0	NaN	NaN	NaN	577028337.75403	845849595.417695	27672156.86	158848216.1	309675137.8	573117457.325	6159292035.0
P/E Ratio	340.0	NaN	NaN	NaN	32.612563	44.348731	2.935451	15.044653	20.819876	31.764755	528.039074
P/B Ratio	340.0	NaN	NaN	NaN	-1.718249	13.966912	-76.119077	-4.352056	-1.06717	3.917066	129.064585

	case	profitable	reinvesting profits into growth	paying dividend	case.1	issue
0	1	y	y	y/n	1	NaN
1	2	n	y	n	2	no profits to reinvest in growth
2	3	n	y	y	3	don't dividends get paid out of profits? (Also...
3	4	y	y	y	4	NaN
4	5	y	y	n	5	NaN
5	6	y	n	y	6	NaN
6	7	y	n	n	7	NaN
7	8	NaN	NaN	NaN	8	NaN

	Elements	Reference	December 31, 2021	December 31, 2020
0	Assets	NaN	NaN	NaN
1	Current assets	NaN	NaN	NaN
2	Cash and cash equivalents	NaN	6,802	4,364
3	Notes and accounts receivable - net	6.0	32,383	20,581
4	Inventories	NaN	NaN	NaN
5	Crude oil, products and merchandise	3.0	14,519	14,169
6	Materials and supplies	NaN	4,261	4,681
7	Other current assets	NaN	1,189	1,098
8	Total current assets	NaN	59,154	44,893
9	Investments, advances and long-term receivables	8.0	45,195	43,515
10	Property, plant and equipment, at cost, less a...	9.0	216,552	227,553
11	Other assets, including intangibles - net	NaN	18,022	16,789
12	Total assets	NaN	338,923	332,750
13	NaN	NaN	NaN	NaN
14	Liabilities	NaN	NaN	NaN
15	Current liabilities	NaN	NaN	NaN
16	Notes and loans payable	6.0	4,276	20,458
17	Accounts payable and accrued liabilities	6.0	50,766	35,221
18	Income taxes payable	NaN	1,601	684
19	Total current liabilities	NaN	56,643	56,363
20	Long-term debt	14.0	43,428	47,182
21	Postretirement benefits reserves	17.0	18,430	22,415
22	Deferred income tax liabilities	19.0	20,165	18,165
23	Long-term obligations to equity companies	NaN	2,857	3,253
24	Other long-term obligations	NaN	21,717	21,242
25	Total liabilities	NaN	163,240	168,620
26	NaN	NaN	NaN	NaN
27	Commitments and contingencies	16.0	NaN	NaN
28	NaN	NaN	NaN	NaN
29	Equity	NaN	NaN	NaN
30	Common stock without par value	NaN	15,746	15,688
31	(9,000 million shares authorized, 8,019 millio...	NaN	NaN	NaN
32	Earnings reinvested	NaN	392,059	383,943
33	Accumulated other comprehensive income	NaN	-13764	-16705
34	Common stock held in treasury	NaN	-225464	-225776
35	(3,780 million shares in 2021 and 3,786 millio...	NaN	NaN	NaN
36	ExxonMobil share of equity	NaN	168,577	157,150
37	Noncontrolling interests	NaN	7,106	6,980
38	Total equity	NaN	175,683	164,130
39	Total liabilities and equity	NaN	338,923	332,750
40	NaN	NaN	NaN	NaN
41	The information in the Notes to Consolidated F...	NaN	NaN	NaN
42	72	NaN	NaN	NaN

	Current Price	Price Change	Volatility	ROE	Cash Ratio	Net Cash Flow	Net Income	Earnings Per Share	Estimated Shares Outstanding	P/E Ratio	P/B Ratio
0	-0.393341	0.493950	0.272749	0.989601	-0.210698	-0.339355	1.554415	1.309399	0.107863	-0.652487	-0.506653
1	-0.220837	0.355439	1.137045	0.937737	0.077269	-0.002335	0.927628	0.056755	1.250274	-0.311769	-0.504205
2	-0.367195	0.602479	-0.427007	-0.192905	-0.033488	0.454058	0.744371	0.024831	1.098021	-0.391502	0.094941
3	0.133567	0.825696	-0.284802	-0.317379	1.218059	-0.152497	-0.219816	-0.230563	-0.091622	0.947148	0.424333
4	-0.260874	-0.492636	0.296470	-0.265515	2.237018	0.133564	-0.202703	-0.374982	1.978399	3.293307	0.199196
...	...	...	...	...	...	...	...	...	...	...	...
335	-0.486181	0.901646	0.540121	-0.255142	4.308162	-0.559673	-1.487784	-1.127481	0.429111	-0.082116	0.572194
336	-0.289510	-1.065766	-0.079703	1.062211	-0.476513	0.053235	-0.051186	0.029391	-0.167741	-0.337154	-0.152012
337	0.221913	0.439539	-0.206067	-0.400362	0.332009	0.164889	-0.342467	-0.303532	-0.460058	2.233634	-1.589390
338	-0.547053	-0.436811	-0.097813	-0.369243	0.320933	-0.051022	-0.301171	-0.239684	-0.377852	-0.222714	0.118680
339	-0.336453	1.051046	0.142671	-0.078803	-0.055639	0.111378	-0.293666	-0.318734	-0.092942	0.854902	0.246754

	Ticker Symbol	Security	GICS Sector	GICS Sub Industry	Current Price	Price Change	Volatility	ROE	Cash Ratio	Net Cash Flow	Net Income	Earnings Per Share	Estimated Shares Outstanding	P/E Ratio	P/B Ratio
0	AAL	American Airlines Group	Industrials	Airlines	42.349998	9.999995	1.687151	135	51	-604000000	7610000000	11.39	6.681299e+08	3.718174	-8.784219
1	ABBV	AbbVie	Health Care	Pharmaceuticals	59.240002	8.339433	2.197887	130	77	51000000	5144000000	3.15	1.633016e+09	18.806350	-8.750068

	Ticker Symbol	Security	GICS Sector	GICS Sub Industry	Current Price	Price Change	Volatility	ROE	Cash Ratio	Net Cash Flow	Net Income	Earnings Per Share	Estimated Shares Outstanding	P/E Ratio	P/B Ratio
102	DVN	Devon Energy Corp.	Energy	Oil & Gas Exploration & Production	32.000000	-15.478079	2.923698	205	70	830000000	-14454000000	-35.55	4.065823e+08	93.089287	1.785616
125	FB	Facebook	Information Technology	Internet Software & Services	104.660004	16.224320	1.320606	8	958	592000000	3669000000	1.31	2.800763e+09	79.893133	5.884467
11	AIV	Apartment Investment & Mgmt	Real Estate	REITs	40.029999	7.578608	1.163334	15	47	21818000	248710000	1.52	1.636250e+08	26.335526	-1.269332
248	PG	Procter & Gamble	Consumer Staples	Personal Products	79.410004	10.660538	0.806056	17	129	160383000	636056000	3.28	4.913916e+08	24.070121	-2.256747

					Current Price	Price Change	Volatility	ROE	Cash Ratio	Net Cash Flow	Net Income	Earnings Per Share	Estimated Shares Outstanding	P/E Ratio	P/B Ratio
KM_segments	Ticker Symbol	Security	GICS Sector	GICS Sub Industry
0	AAL	American Airlines Group	Industrials	Airlines	42.349998	9.999995	1.687151	135.0	51.0	-604000000.0	7.610000e+09	11.39	6.681299e+08	3.718174	-8.784219
	ABBV	AbbVie	Health Care	Pharmaceuticals	59.240002	8.339433	2.197887	130.0	77.0	51000000.0	5.144000e+09	3.15	1.633016e+09	18.806350	-8.750068
	ABT	Abbott Laboratories	Health Care	Health Care Equipment	44.910000	11.301121	1.273646	21.0	67.0	938000000.0	4.423000e+09	2.94	1.504422e+09	15.275510	-0.394171
	ADM	Archer-Daniels-Midland Co	Consumer Staples	Agricultural Products	36.680000	-12.017268	1.516493	10.0	49.0	-189000000.0	1.849000e+09	2.99	6.183946e+08	12.267559	7.496831
	AEE	Ameren Corp	Utilities	MultiUtilities	43.230000	2.174424	1.124186	9.0	14.0	287000000.0	6.360000e+08	2.60	2.446154e+08	16.626923	-0.719497
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
5	SE	Spectra Energy Corp.	Energy	Oil & Gas Refining & Marketing & Transportation	23.940001	-9.898378	2.030786	3.0	6.0	-2000000.0	1.960000e+08	0.29	6.758621e+08	82.551728	-2.580408
	SWN	Southwestern Energy	Energy	Oil & Gas Exploration & Production	7.110000	-44.798137	4.580042	200.0	2.0	-38000000.0	-4.556000e+09	-6.07	4.021417e+08	93.089287	1.273530
	TDC	Teradata Corp.	Information Technology	Application Software	26.420000	-8.833678	2.730659	25.0	88.0	5000000.0	-2.140000e+08	-1.53	1.398693e+08	74.555557	4.068084
	WMB	Williams Cos.	Energy	Oil & Gas Exploration & Production	25.700001	-30.988186	3.719560	9.0	4.0	-140000000.0	-5.710000e+08	-0.76	7.513158e+08	93.089287	-14.561121
	XEC	Cimarex Energy	Energy	Oil & Gas Exploration & Production	89.379997	-14.403372	2.397940	86.0	190.0	373520000.0	-2.408948e+09	-25.92	9.293781e+07	93.089287	7.186128

	Current Price	Price Change	Volatility	ROE	Cash Ratio	Net Cash Flow	Net Income	Earnings Per Share	Estimated Shares Outstanding	P/E Ratio	P/B Ratio
0	False	False	False	False	False	False	False	False	False	False	False
1	False	False	False	False	False	False	False	False	False	False	False
2	False	False	False	False	False	False	False	False	False	False	False
3	False	False	False	False	False	False	False	False	False	False	False
4	False	False	False	False	False	False	False	False	False	False	False
...	...	...	...	...	...	...	...	...	...	...	...
335	False	False	False	False	False	False	False	False	False	False	False
336	False	False	False	False	False	False	False	False	False	False	False
337	False	False	False	False	False	False	False	False	False	False	False
338	False	False	False	False	False	False	False	False	False	False	False
339	False	False	False	False	False	False	False	False	False	False	False

	Linkage	Cophenetic Coefficient
4	ward	0.710118
1	complete	0.787328
5	weighted	0.869378
0	single	0.923227
3	centroid	0.931401
2	average	0.942254

					Current Price	Price Change	Volatility	ROE	Cash Ratio	Net Cash Flow	Net Income	Earnings Per Share	Estimated Shares Outstanding	P/E Ratio	P/B Ratio	count_in_each_segment
HC_segments	Ticker Symbol	Security	GICS Sector	GICS Sub Industry
0	AAL	American Airlines Group	Industrials	Airlines	42.349998	9.999995	1.687151	135.0	51.0	-604000000.0	7.610000e+09	11.39	6.681299e+08	3.718174	-8.784219	1
	ABBV	AbbVie	Health Care	Pharmaceuticals	59.240002	8.339433	2.197887	130.0	77.0	51000000.0	5.144000e+09	3.15	1.633016e+09	18.806350	-8.750068	1
	ABT	Abbott Laboratories	Health Care	Health Care Equipment	44.910000	11.301121	1.273646	21.0	67.0	938000000.0	4.423000e+09	2.94	1.504422e+09	15.275510	-0.394171	1
	ADBE	Adobe Systems Inc	Information Technology	Application Software	93.940002	13.977195	1.357679	9.0	180.0	-240840000.0	6.295510e+08	1.26	4.996437e+08	74.555557	4.199651	1
	ADI	Analog Devices, Inc.	Information Technology	Semiconductors	55.320000	-1.827858	1.701169	14.0	272.0	315120000.0	6.968780e+08	0.31	2.247994e+09	178.451613	1.059810	1

					Current Price	Price Change	Volatility	ROE	Cash Ratio	Net Cash Flow	Net Income	Earnings Per Share	Estimated Shares Outstanding	P/E Ratio	P/B Ratio	count_in_each_segment
KM_segments	Ticker Symbol	Security	GICS Sector	GICS Sub Industry
0	AAL	American Airlines Group	Industrials	Airlines	42.349998	9.999995	1.687151	135.000000	51.000000	-604000000.000000	7610000000.000000	11.390000	668129938.500000	3.718174	-8.784219	1
	ABBV	AbbVie	Health Care	Pharmaceuticals	59.240002	8.339433	2.197887	130.000000	77.000000	51000000.000000	5144000000.000000	3.150000	1633015873.000000	18.806350	-8.750068	1
	ABT	Abbott Laboratories	Health Care	Health Care Equipment	44.910000	11.301121	1.273646	21.000000	67.000000	938000000.000000	4423000000.000000	2.940000	1504421769.000000	15.275510	-0.394171	1
	ADM	Archer-Daniels-Midland Co	Consumer Staples	Agricultural Products	36.680000	-12.017268	1.516493	10.000000	49.000000	-189000000.000000	1849000000.000000	2.990000	618394648.800000	12.267559	7.496831	1
	AEE	Ameren Corp	Utilities	MultiUtilities	43.230000	2.174424	1.124186	9.000000	14.000000	287000000.000000	636000000.000000	2.600000	244615384.600000	16.626923	-0.719497	1
	AEP	American Electric Power	Utilities	Electric Utilities	58.270000	2.371753	1.068485	11.000000	9.000000	13900000.000000	2052300000.000000	3.130000	421897810.200000	18.456543	-3.022649	1
	AFL	AFLAC Inc	Financials	Life & Health Insurance	59.900002	3.027181	1.048295	14.000000	99.000000	-308000000.000000	2533000000.000000	5.880000	430782312.900000	10.187075	-1.883912	1
	AIG	American International Group, Inc.	Financials	Property & Casualty Insurance	61.970001	8.358108	1.106965	2.000000	99.000000	-129000000.000000	2196000000.000000	1.690000	1299408284.000000	36.668640	-4.327138	1
	AIV	Apartment Investment & Mgmt	Real Estate	REITs	40.029999	7.578608	1.163334	15.000000	47.000000	21818000.000000	248710000.000000	1.520000	163625000.000000	26.335526	-1.269332	1
	AIZ	Assurant Inc	Financials	Multi-line Insurance	80.540001	1.897773	1.112604	3.000000	99.000000	-30351000.000000	141555000.000000	2.080000	68055288.460000	38.721154	-4.072615	1
	AJG	Arthur J. Gallagher & Co.	Financials	Insurance Brokers	40.939999	-0.606943	1.052050	10.000000	99.000000	166000000.000000	356800000.000000	2.070000	172367149.800000	19.777777	-9.855706	1
	AKAM	Akamai Technologies Inc	Information Technology	Internet Software & Services	52.630001	-23.790903	1.384502	10.000000	225.000000	50823000.000000	321406000.000000	1.800000	178558888.900000	29.238889	4.282358	1
	ALB	Albemarle Corp	Materials	Specialty Chemicals	56.009998	26.461948	1.974323	10.000000	13.000000	-2276034000.000000	334906000.000000	3.010000	111264451.800000	18.607973	-13.649723	1
	ALK	Alaska Air Group Inc	Industrials	Airlines	80.510002	2.066436	1.773431	35.000000	74.000000	-34000000.000000	848000000.000000	6.610000	128290469.000000	12.180031	-1.114658	1
	ALL	Allstate Corp	Financials	Property & Casualty Insurance	62.090000	6.592275	1.053266	11.000000	99.000000	-162000000.000000	2171000000.000000	5.120000	424023437.500000	12.126953	-4.327138	1
	ALLE	Allegion	Industrials	Building Products	65.919998	13.753230	1.283795	601.000000	45.000000	-90800000.000000	153900000.000000	1.600000	96187500.000000	41.199999	-0.877453	1
	AMAT	Applied Materials Inc	Information Technology	Semiconductor Equipment	18.670000	26.834239	1.460030	18.000000	131.000000	1795000000.000000	1377000000.000000	1.130000	1218584071.000000	16.522124	3.857756	1
	AME	AMETEK Inc	Industrials	Electrical Components & Equipment	53.590000	2.212474	1.089266	18.000000	37.000000	3390000.000000	590859000.000000	2.460000	240186585.400000	21.784553	-4.490342	1
	AMG	Affiliated Managers Group Inc	Financials	Asset Management & Custody Banks	159.759995	-6.611335	2.093065	18.000000	67.000000	13200000.000000	516000000.000000	9.490000	54373024.240000	16.834562	-31.015380	1
	AMP	Ameriprise Financial	Financials	Asset Management & Custody Banks	106.419998	-2.420686	1.222260	22.000000	67.000000	-281000000.000000	1562000000.000000	8.600000	181627907.000000	12.374418	-13.398380	1
	AMT	American Tower Corp A	Real Estate	Specialized REITs	96.949997	10.233087	1.165804	10.000000	39.000000	7194000.000000	685074000.000000	1.420000	482446478.900000	68.274646	-20.813577	1
	AN	AutoNation Inc	Consumer Discretionary	Specialty Stores	59.660000	2.350316	1.480914	19.000000	1.000000	-1300000.000000	442600000.000000	3.930000	112620865.100000	15.180662	-7.970104	1
	ANTM	Anthem Inc.	Health Care	Managed Health Care	139.440002	-0.620053	1.511654	11.000000	70.000000	-38200000.000000	2560000000.000000	9.730000	263103802.700000	14.330935	-31.006773	1
	AON	Aon plc	Financials	Insurance Brokers	92.209999	3.910301	1.105032	23.000000	99.000000	10000000.000000	1385000000.000000	4.930000	280933062.900000	18.703854	-7.759856	1
	APH	Amphenol Corp	Information Technology	Electronic Components	52.230000	2.693667	1.007762	24.000000	175.000000	768300000.000000	763500000.000000	2.470000	309109311.700000	21.145749	8.202923	1
	ATVI	Activision Blizzard	Information Technology	Home Entertainment Software	38.709999	23.319529	1.886335	11.000000	70.000000	-3025000000.000000	892000000.000000	1.210000	737190082.600000	31.991735	0.290291	1
	AVB	AvalonBay Communities, Inc.	Real Estate	Residential REITs	184.130005	4.857630	1.132875	8.000000	47.000000	-108953000.000000	741733000.000000	5.540000	133886823.100000	33.236463	-3.089477	1
	AWK	American Water Works Company Inc	Utilities	Water Utilities	59.750000	8.596874	1.171525	9.000000	4.000000	22000000.000000	476000000.000000	2.660000	178947368.400000	22.462406	-4.895294	1
	AXP	American Express Co	Financials	Consumer Finance	69.550003	-6.216290	0.900066	25.000000	99.000000	474000000.000000	5163000000.000000	3.900000	506660363.000000	10.263506	-0.609074	1
	BA	Boeing Company	Industrials	Aerospace & Defense	144.589996	10.105078	1.155905	82.000000	24.000000	-431000000.000000	5176000000.000000	7.520000	688297872.300000	19.227393	22.032612	1
	BAX	Baxter International Inc.	Health Care	Health Care Equipment	38.150002	16.702365	1.204526	11.000000	128.000000	-712000000.000000	968000000.000000	1.780000	543820224.700000	21.432585	8.637045	1
	BBT	BB&T Corporation	Financials	Banks	37.810001	5.940045	1.077678	8.000000	99.000000	1386000000.000000	2084000000.000000	2.590000	804633204.600000	14.598456	-0.852562	1
	BCR	Bard (C.R.) Inc.	Health Care	Health Care Equipment	189.440002	1.549182	1.394436	9.000000	82.000000	-9600000.000000	135400000.000000	1.800000	75222222.220000	105.244446	-4.127770	1
	BIIB	BIOGEN IDEC Inc.	Health Care	Biotechnology	306.350006	4.917982	1.825994	38.000000	133.000000	148900000.000000	3547000000.000000	15.380000	230624187.300000	19.918726	0.162602	1
	BK	The Bank of New York Mellon Corp.	Financials	Banks	41.220001	5.422003	1.201660	8.000000	99.000000	-433000000.000000	3158000000.000000	2.730000	1156776557.000000	15.098901	-3.321298	1
	BLL	Ball Corp	Materials	Metal & Glass Containers	72.730003	16.535816	1.386684	22.000000	10.000000	32600000.000000	280900000.000000	2.050000	137024390.200000	35.478050	-3.895657	1
	BMY	Bristol-Myers Squibb	Health Care	Health Care Distributors	68.790001	16.081680	1.498872	11.000000	53.000000	-3186000000.000000	1565000000.000000	0.940000	1664893617.000000	73.180852	0.588026	1
	BSX	Boston Scientific	Health Care	Health Care Equipment	18.440001	11.757582	1.491764	4.000000	13.000000	-268000000.000000	-239000000.000000	-0.180000	1327777778.000000	31.468962	-3.880921	1
	BWA	BorgWarner	Consumer Discretionary	Auto Parts & Equipment	43.230000	3.470563	2.058769	17.000000	25.000000	-220100000.000000	609700000.000000	2.720000	224154411.800000	15.893382	1.044815	1

					Current Price	Price Change	Volatility	ROE	Cash Ratio	Net Cash Flow	Net Income	Earnings Per Share	Estimated Shares Outstanding	P/E Ratio	P/B Ratio	count_in_each_segment
HC_segments	Ticker Symbol	Security	GICS Sector	GICS Sub Industry
0	AAL	American Airlines Group	Industrials	Airlines	42.349998	9.999995	1.687151	135.000000	51.000000	-604000000.000000	7610000000.000000	11.390000	668129938.500000	3.718174	-8.784219	1
	ABBV	AbbVie	Health Care	Pharmaceuticals	59.240002	8.339433	2.197887	130.000000	77.000000	51000000.000000	5144000000.000000	3.150000	1633015873.000000	18.806350	-8.750068	1
	ABT	Abbott Laboratories	Health Care	Health Care Equipment	44.910000	11.301121	1.273646	21.000000	67.000000	938000000.000000	4423000000.000000	2.940000	1504421769.000000	15.275510	-0.394171	1
	ADBE	Adobe Systems Inc	Information Technology	Application Software	93.940002	13.977195	1.357679	9.000000	180.000000	-240840000.000000	629551000.000000	1.260000	499643650.800000	74.555557	4.199651	1
	ADI	Analog Devices, Inc.	Information Technology	Semiconductors	55.320000	-1.827858	1.701169	14.000000	272.000000	315120000.000000	696878000.000000	0.310000	2247993548.000000	178.451613	1.059810	1
	ADM	Archer-Daniels-Midland Co	Consumer Staples	Agricultural Products	36.680000	-12.017268	1.516493	10.000000	49.000000	-189000000.000000	1849000000.000000	2.990000	618394648.800000	12.267559	7.496831	1
	ADS	Alliance Data Systems	Information Technology	Data Processing & Outsourced Services	276.570007	6.189286	1.116976	30.000000	25.000000	90885000.000000	596541000.000000	8.910000	66951851.850000	31.040405	129.064585	1
	AEE	Ameren Corp	Utilities	MultiUtilities	43.230000	2.174424	1.124186	9.000000	14.000000	287000000.000000	636000000.000000	2.600000	244615384.600000	16.626923	-0.719497	1
	AEP	American Electric Power	Utilities	Electric Utilities	58.270000	2.371753	1.068485	11.000000	9.000000	13900000.000000	2052300000.000000	3.130000	421897810.200000	18.456543	-3.022649	1
	AFL	AFLAC Inc	Financials	Life & Health Insurance	59.900002	3.027181	1.048295	14.000000	99.000000	-308000000.000000	2533000000.000000	5.880000	430782312.900000	10.187075	-1.883912	1
	AIG	American International Group, Inc.	Financials	Property & Casualty Insurance	61.970001	8.358108	1.106965	2.000000	99.000000	-129000000.000000	2196000000.000000	1.690000	1299408284.000000	36.668640	-4.327138	1
	AIV	Apartment Investment & Mgmt	Real Estate	REITs	40.029999	7.578608	1.163334	15.000000	47.000000	21818000.000000	248710000.000000	1.520000	163625000.000000	26.335526	-1.269332	1
	AIZ	Assurant Inc	Financials	Multi-line Insurance	80.540001	1.897773	1.112604	3.000000	99.000000	-30351000.000000	141555000.000000	2.080000	68055288.460000	38.721154	-4.072615	1
	AJG	Arthur J. Gallagher & Co.	Financials	Insurance Brokers	40.939999	-0.606943	1.052050	10.000000	99.000000	166000000.000000	356800000.000000	2.070000	172367149.800000	19.777777	-9.855706	1
	AKAM	Akamai Technologies Inc	Information Technology	Internet Software & Services	52.630001	-23.790903	1.384502	10.000000	225.000000	50823000.000000	321406000.000000	1.800000	178558888.900000	29.238889	4.282358	1
	ALB	Albemarle Corp	Materials	Specialty Chemicals	56.009998	26.461948	1.974323	10.000000	13.000000	-2276034000.000000	334906000.000000	3.010000	111264451.800000	18.607973	-13.649723	1
	ALK	Alaska Air Group Inc	Industrials	Airlines	80.510002	2.066436	1.773431	35.000000	74.000000	-34000000.000000	848000000.000000	6.610000	128290469.000000	12.180031	-1.114658	1
	ALL	Allstate Corp	Financials	Property & Casualty Insurance	62.090000	6.592275	1.053266	11.000000	99.000000	-162000000.000000	2171000000.000000	5.120000	424023437.500000	12.126953	-4.327138	1
	ALLE	Allegion	Industrials	Building Products	65.919998	13.753230	1.283795	601.000000	45.000000	-90800000.000000	153900000.000000	1.600000	96187500.000000	41.199999	-0.877453	1
	ALXN	Alexion Pharmaceuticals	Health Care	Biotechnology	190.750000	22.338380	2.022921	2.000000	195.000000	66000000.000000	144000000.000000	0.680000	211764705.900000	280.514706	-14.171389	1
	AMAT	Applied Materials Inc	Information Technology	Semiconductor Equipment	18.670000	26.834239	1.460030	18.000000	131.000000	1795000000.000000	1377000000.000000	1.130000	1218584071.000000	16.522124	3.857756	1
	AME	AMETEK Inc	Industrials	Electrical Components & Equipment	53.590000	2.212474	1.089266	18.000000	37.000000	3390000.000000	590859000.000000	2.460000	240186585.400000	21.784553	-4.490342	1
	AMG	Affiliated Managers Group Inc	Financials	Asset Management & Custody Banks	159.759995	-6.611335	2.093065	18.000000	67.000000	13200000.000000	516000000.000000	9.490000	54373024.240000	16.834562	-31.015380	1
	AMGN	Amgen Inc	Health Care	Biotechnology	162.330002	17.163478	1.630259	25.000000	362.000000	413000000.000000	6939000000.000000	9.150000	758360655.700000	17.740984	24.012322	1
	AMP	Ameriprise Financial	Financials	Asset Management & Custody Banks	106.419998	-2.420686	1.222260	22.000000	67.000000	-281000000.000000	1562000000.000000	8.600000	181627907.000000	12.374418	-13.398380	1
	AMT	American Tower Corp A	Real Estate	Specialized REITs	96.949997	10.233087	1.165804	10.000000	39.000000	7194000.000000	685074000.000000	1.420000	482446478.900000	68.274646	-20.813577	1
	AMZN	Amazon.com Inc	Consumer Discretionary	Internet & Direct Marketing Retail	675.890015	32.268105	1.460386	4.000000	58.000000	1333000000.000000	596000000.000000	1.280000	465625000.000000	528.039074	3.904430	1
	AN	AutoNation Inc	Consumer Discretionary	Specialty Stores	59.660000	2.350316	1.480914	19.000000	1.000000	-1300000.000000	442600000.000000	3.930000	112620865.100000	15.180662	-7.970104	1
	ANTM	Anthem Inc.	Health Care	Managed Health Care	139.440002	-0.620053	1.511654	11.000000	70.000000	-38200000.000000	2560000000.000000	9.730000	263103802.700000	14.330935	-31.006773	1
	AON	Aon plc	Financials	Insurance Brokers	92.209999	3.910301	1.105032	23.000000	99.000000	10000000.000000	1385000000.000000	4.930000	280933062.900000	18.703854	-7.759856	1
	APA	Apache Corporation	Energy	Oil & Gas Exploration & Production	44.470001	11.397804	2.405408	917.000000	80.000000	698000000.000000	-23528000000.000000	-61.200000	384444444.400000	93.089287	4.970809	1
	APC	Anadarko Petroleum Corp	Energy	Oil & Gas Exploration & Production	48.580002	-20.802083	2.435165	52.000000	22.000000	-6430000000.000000	-6692000000.000000	-13.180000	507738998.500000	93.089287	-12.860938	1
	APH	Amphenol Corp	Information Technology	Electronic Components	52.230000	2.693667	1.007762	24.000000	175.000000	768300000.000000	763500000.000000	2.470000	309109311.700000	21.145749	8.202923	1
	ARNC	Arconic Inc	Industrials	Aerospace & Defense	7.398807	1.647784	2.592065	3.000000	37.000000	42000000.000000	-322000000.000000	-0.310000	1038709677.000000	18.687607	2.639814	1
	ATVI	Activision Blizzard	Information Technology	Home Entertainment Software	38.709999	23.319529	1.886335	11.000000	70.000000	-3025000000.000000	892000000.000000	1.210000	737190082.600000	31.991735	0.290291	1
	AVB	AvalonBay Communities, Inc.	Real Estate	Residential REITs	184.130005	4.857630	1.132875	8.000000	47.000000	-108953000.000000	741733000.000000	5.540000	133886823.100000	33.236463	-3.089477	1
	AVGO	Broadcom	Information Technology	Semiconductors	145.149994	17.902683	1.847180	29.000000	163.000000	218000000.000000	1364000000.000000	2.860000	1044055944.000000	18.249407	3.954975	1
	AWK	American Water Works Company Inc	Utilities	Water Utilities	59.750000	8.596874	1.171525	9.000000	4.000000	22000000.000000	476000000.000000	2.660000	178947368.400000	22.462406	-4.895294	1
	AXP	American Express Co	Financials	Consumer Finance	69.550003	-6.216290	0.900066	25.000000	99.000000	474000000.000000	5163000000.000000	3.900000	506660363.000000	10.263506	-0.609074	1

Prelim info¶

Objective¶

Data Dictionary¶

Prelim analysis¶

list 4 object columns¶

Import libraries¶

Load data¶

Data Overview¶

Data Overview: initial steps:¶

data:¶

Check data shape¶

Show few rows of data¶

data type per col¶

data.info()¶

Create copy of original data¶

Check for duplicates and missing values¶

df.duplicated().sum() = 0¶

df.isna().sum() = 0 / each col¶

end of section: df.isna().sum() = 0 / each col¶

GitHub milestones¶

Put na counts keys and values in a dictionary¶

get .keys() for na_count (per column) dictionary:¶

get .values() for na_count (per column) dictionary:¶

To check no na values [Resolved]:¶

Statistical summary¶

object_list for object type columns¶

statistical summary using describe() method:¶

observations:¶

Flagged 4 features, passed them to object_list for ease of reference, as follows:¶

Exploratory Data Analysis¶

Univariate analysis¶

matplotlib.pyplot.subplots / plt.subplots¶

Returns:¶

end of notes on matplotlib.pyplot.subplots / plt.subplots¶

seaborn notes:¶

sns.boxplot¶

sns.histplot¶

end of notes on seaborn¶

matplotlib.pyplot.axvline¶

Parameters:¶

Returns:¶

Other Parameters:¶

end of matplotlib.pyplot.axvline notes section¶

def histogram_boxplot():¶

show histogram_boxplots:¶

Current Price¶

Current stock price:¶

call .describe() on ['Price Change'] column and explain output:¶

Explore different methods to Compute Quantiles:¶

pandas.describe()¶

explanation:¶

Parameters:¶

limit to 2 decimal places:¶

[Task:] Get IQR with pandas.¶

Note:¶

calculating quartiles:¶

end of this section of calculating quartiles¶

calculating quantiles continued: Inverse CDF Method:¶

Inverse CDF (in the context of calculating quantiles)¶

end of limit to 2 decimal places section¶

end of Section: Explore different methods to Compute Quantiles¶

Price Change¶

call .describe() on ['Price Change'] column and explain output:¶

string formatting and casting to float not int:¶

begin assembly of 5 explanatory lines on summary statistics (to be reused for each plot):¶

non-automated description of plot:¶

Volatility¶

ROE¶

Cash Ratio¶

Net Cash Flow¶

Net Income¶

Earnings Per Share¶

Estimated Shares Outstanding¶

P/E Ratio¶

P/B Ratio¶

end of histogram_boxplots¶

def labeled_barplot():¶

labeled_barplots¶

GICS Sector¶

GICS Sub-Industry Issue: Convert Series to DataFrame and Get output of Index values:¶

statistical summary using `describe()` method:¶

`Current Price`¶

`Price Change`¶

`Volatility`¶

`ROE`¶

`Cash Ratio`¶

`Net Cash Flow`¶

`Net Income`¶

`Earnings Per Share`¶

`Estimated Shares Outstanding`¶

`P/E Ratio`¶

`P/B Ratio`¶

`GICS Sector`¶

`GICS Sub-Industry` Issue: Convert Series to DataFrame and Get output of Index values:¶

Solution: to `GICS Sub-Industry` Issue: Convert Series to DataFrame and Get output of Index values:¶

`GICS Sub Industry`¶